Query like IN that does - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Query like IN that does

Author

Topic

JLM
Starting Member

6 Posts

Posted - 2006-02-08 : 15:42:12

Is there a way I can pass in a list of data and do a query much like using 'IN' to retreive information that matches all of the items passed in?

Example:
select distinct CarID from Car_Styles_j where StyleID IN ('5, 10, 56')

The IN phrase finds all cars that have the CarStyleID of 5 or 10 or 56, but I want to find cars that have all 3 styles. This information is stored in a junction table so the table would look something like:

CarID StyleID
----- ----------
1 5
1 10
1 56
2 5
3 10
4 56

So in this instance, I only want back Car 1 back.

I appreciate any help you can provide.

--JLM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-08 : 16:04:46

select CarID
from Car_Styles_j
where StyleID IN ('5, 10, 56')
group by CarID
having count(*) = 3

or count(distinct StyleID) = 3 if they aren't unique.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JLM
Starting Member

6 Posts

Posted - 2006-02-08 : 17:09:16

nr,

Thank you for taking the time to reply, that works perfect. (Its always the simple thing that get you!)

JLM

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-09 : 07:25:50

With an non-clustered index on styleId and clustered index on carID and significant number of rows next solution should be faster:

select s5.carID
from car_styles_j s5
join car_styles_j s10 on s10.carID = s5.carID
join car_styles_j s56 on s56.carID = s5.carID
where s5.styleID = 5 and
      s10.styleID = 10 and
      s56.styleID = 56

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources