Select IN? - SQL Server Forums

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

Select IN?

Author

Topic

golyath
Starting Member

21 Posts

Posted - 2008-02-13 : 04:40:50

Hi,

Im trying to do this select statement but im a little confused, so any help would be greatly appreciated!

I have table:

ID,
CODE,
MODE

I want to select the MODE using say three CODEs, so i could use:

SELECT MODE WHERE CODE IN ('Code1','Code2','Code3');

This will return all records where any of the 3 CODEs appear. But i actually only want to select the MODE that have a record for all three CODEs.

So if we find three records (one for each of the CODEs), with the same MODE then we want that MODE but if we dont have all three we dont want it.

Am i making sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 04:48:26

[code]SELECT Mode
FROM Table1
GROUP BY Mode
HAVING MAX(CASE WHEN Code = 'Code1' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Code = 'Code2' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Code = 'Code3' THEN 1 ELSE 0 END) = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 04:55:29

or:-

select mode from table
WHERE code='c1'
intersect
select mode from table
WHERE code='c2'
intersect
select mode from table
WHERE code='c3'

golyath
Starting Member

21 Posts

Posted - 2008-02-13 : 05:12:44

Thanks i will try both

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 05:22:52

sql 2000 compatible soln:-

SELECT Mode FROM
(
SELECT Mode,COUNT(DISTINCT Code) as SumCode FROM @t
WHERE Code in ('c1','c2','c3') 
GROUP BY Mode)t
WHERE SumCode =3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 05:25:45

Yes, please do and post back timings here.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 05:28:36

quote:

Originally posted by visakh16

SELECT Mode FROM
(
SELECT Mode,COUNT(DISTINCT Code) as SumCode FROM @t
WHERE Code in ('c1','c2','c3') 
GROUP BY Mode)t
WHERE SumCode =3

SELECT		Mode
FROM		Table1
WHERE		Code IN ('c1', 'c2', 'c3') 
GROUP BY	Mode
HAVING		COUNT(DISTINCT Code) = 3

I do believe COUNT(DISTINCT ...) operator is very costly.
I will wait to see OP post back time taken to run each suggestion.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources