Multiple In Statements - 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)

Multiple In Statements

Author

Topic

kristian
Starting Member

21 Posts

Posted - 2002-08-14 : 06:09:40

Hi Everyone

Im using code like below and i wondered if there was any way of improving it by not using 4 select statements.

SELECT * WHERE
Cat1 IN (SELECT Category FROM Categories)
OR Cat2 IN (SELECT Category FROM Categories)
OR SubCat1 IN (SELECT Category FROM Categories)
OR SubCat2 IN (SELECT Category FROM Categories)
)

Perhaps something like
SELECT * WHERE
Cat1, Cat2, SubCat1, SubCat2 IN (SELECT Category FROM Categories))

Or
SELECT * WHERE
(Cat1 OR Cat2 OR SubCat1 OR SubCat2) IN (SELECT Category FROM Categories))

You can see what im getting at, I dont want to know about normalizing, denormalizing, joins and table design. Im just interested in finding out if you can have severel fields match an in statement without repeating the in statment.

PS, If you dont feel you can answer this without seeeing table scheme and data etc then please just ignore it.

Thanks

Kristian

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-14 : 06:43:26


SELECT *
FROM Things
WHERE EXISTS (
    SELECT *
    FROM Categories
    WHERE Category IN (Cat1, Cat2, SubCat1, SubCat2)
  )

However, you may well find the ORs work better.

Edited by - Arnold Fribble on 08/14/2002 06:45:26

kristian
Starting Member

21 Posts

Posted - 2002-08-14 : 07:19:12

Thanks, ill check that out

Kristian

kristian
Starting Member

21 Posts

Posted - 2002-08-14 : 07:29:22

Yep, i got that working, i gone from an execution plan of about 20 entries to one with about 5.

Thanks

Kristian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources