Trying to figure out appropriate "Where" clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to figure out appropriate "Where" clause

Author

Topic

alwayslearning
Starting Member

2 Posts

Posted - 2011-04-15 : 01:49:47

Hey everyone - had a question about this sql query I'm trying to write. I'm really having some difficulty writing the "where" clause. Any help would be greatly appreciated!

Imagine that all of the following resides in the same table:

[Table A]
Column A
Greg
Sue
Sam
Greg
Sue
Tom
Frank
Greg

Column B
Red
Blue
Pink
Blue
Red
Green
Pink
Pink

Column C
123
9432
23890
23894
328
2308
23840
20438

SELECT Column A, Column B, Column C
FROM [TABLE A]
WHERE (i don't know this part....where the people in column A has "red" and "blue" record)

The results should be this:

Column A
Greg
Greg
Sue
Sue

Column B
Red
Blue
Blue
Red

Column C
123
23894
9432
328

Any help would be greatly appreciated!

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-15 : 02:25:41

Try this

select * from TableA where ColumnB='Red' or ColumnB='Blue'

Raghu' S

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-04-15 : 06:35:07

also

select *
from TableA
where ColumnB in('Red','Blue')

--Ranjit

alwayslearning
Starting Member

2 Posts

Posted - 2011-04-15 : 13:39:40

Thanks for the response! Using the Where...in clause brings back people who had red or blue. I'd like to return the people who identified "red" AND "blue".

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-15 : 13:48:59

DECLARE @TableA TABLE (ColA varchar(10),COLB varchar(10))
INSERT INTO @TABLEA (ColA,COLB)
SELECT 'Greg' ,'Red' UNION ALL
SELECT 'Sue','Blue' UNION ALL
SELECT 'Sam','Pink' UNION ALL
SELECT 'Greg','Blue' UNION ALL
SELECT 'Sue','Red' UNION ALL
SELECT 'Tom','Green' UNION ALL
SELECT 'Frank' ,'Pink'UNION ALL
SELECT 'Greg','Pink'

SELECT ColA
FROM @tablea

GROUP BY Cola

HAVING MAX(CASE WHEN ColB = 'Red' THEN 1 ELSE 0 END ) + MAX(CASE WHEN ColB = 'Blue' THEN 1 ELSE 0 END) = 2

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources