December 30, 2009 at 8:12 am
What would be the SQL statement to select a project number base on the colors used in that project e.g. I want to select all the proj #s that use the colors Blue, Red and Green. For this table the result would be proj = 100.
Sometimes I only need to select a proj# base on just 1 color, other times multiple colors.
Thanks in advance
ID|Proj|Color
------------
1 |100|Blue
2 |100|Red
3 |101|Green
4 |102|Purple
5 |103|White
6 |100|Green
.
.
December 30, 2009 at 8:15 am
Do you know that "where" clause exists in SQL Server?
Use it, if not look in Books Online for it. 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 9:42 am
Im aware of it. The following statement returns nothing
SELECT PROJ
FROM TABLE
WHERE COLOR = 'BLUE' AND COLOR = 'RED' AND COLOR = 'GREEN'
and the following statement returns an error
SELECT PROJ
FROM TABLE
WHERE COLOR = 'BLUE' AND 'RED' AND 'GREEN'
December 30, 2009 at 9:56 am
Instead of using AND use OR.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 10:00 am
You could also use this way
Where COLOR IN ('RED','BLUE','GREEN' )
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 10:03 am
That returns proj 100 and 101. Only proj 100 uses the colors BLUE, RED and GREEN.
December 30, 2009 at 10:05 am
For your problem it would be better to add a column for each color. That way you can mark for each project what color it uses.
ID | Proj | WHITE | GREEN | ....
1 100 x x
2 101 x
Query:
SELECT Proj WHERE WHITE = 'x' AND GREEN = 'x' ....
December 30, 2009 at 10:16 am
cclancy (12/30/2009)
That returns proj 100 and 101. Only proj 100 uses the colors BLUE, RED and GREEN.
To get tested answers you need to provide the DDL and sample data.
Read the article and post your question again. http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 30, 2009 at 10:36 am
cclancy, you could try something like this...
IF OBJECT_ID('TempDB..#projects','u') IS NOT NULL
DROP TABLE #projects
CREATE TABLE #projects
(
ID INT IDENTITY(1,1),
Proj INT,
Color CHAR(10)
)
INSERT INTO #projects
SELECT 100,'Blue' UNION ALL
SELECT 100,'Red' UNION ALL
SELECT 101,'Green' UNION ALL
SELECT 102,'Purple' UNION ALL
SELECT 103,'White' UNION ALL
SELECT 100,'Green'
SELECT
Proj
FROM
(
SELECT
Proj,
Blue = SUM(CASE WHEN color = 'Blue' THEN 1 ELSE 0 END),
Green = SUM(CASE WHEN color = 'Green' THEN 1 ELSE 0 END),
Purple = SUM(CASE WHEN color = 'Purple' THEN 1 ELSE 0 END),
Red = SUM(CASE WHEN color = 'Red' THEN 1 ELSE 0 END),
White = SUM(CASE WHEN color = 'White' THEN 1 ELSE 0 END)
FROM #projects
GROUP BY proj
) t1
WHERE blue + red + green = 3
However, if you have many colors, you would want to turn this into a variable column query with input parameters.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 5, 2010 at 8:10 am
Don't be ironic, Bru. Do help him or not, and if you don't let another do it. Your comment is an unnecessary narcissism.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply