TSQL-Multiple AND condition on single column

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)

TSQL-Multiple AND condition on single column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-07 : 09:23:27

raghu writes "Sample query attached,Using SQL Server 7.0,SP2


set nocount on
CREATE TABLE #temp
(Location int null)

Insert into #temp values(1)
Insert into #temp values(2)
Insert into #temp values(3)
Insert into #temp values(4)

--select * from #temp

select * from 
#temp where location=1 and location=2
/*This won't work*/

drop table #temp

In the above table #temp, I need to query the location field on multiple condition.

Example when i run 
select * from 
#temp where location=1 and location=2 and location=3

I need the result set to return the location that matched ALL CONDITIONS in this case "location=1 and location=2 and location=3", even if one of location fails the query should return 0 rows.
If i write the quey example
select * from
#temp where location=1 and location=2 and location=6 it should return me 0 rows, since 6 is not found in the table #temp

Thanks
Raghu"

ToddV
Posting Yak Master

218 Posts

Posted - 2001-12-07 : 11:17:24

I think maybe their is something missing in your test setup. I am guessing that what you want to be able to do is to tell all the locations where all conditions are present. If this is true I suspect this provides a more accurate example:

CREATE TABLE #temp(Location int null,
Condition INT )

Insert into #temp values(1,1)
Insert into #temp values(1,2)
Insert into #temp values(1,3)
Insert into #temp values(1,4)

Insert into #temp values(2,1)

Insert into #temp values(3,2)
Insert into #temp values(3,2)

Insert into #temp values(4,1)
Insert into #temp values(4,3)
Insert into #temp values(4,4)

If this is the case, This code will do what you want:

SELECT Location
FROM #TEMP
GROUP BY Location
Having SUM(CASE WHEN Condition = 1 THEN 1 ELSE 0 END)>0 and
SUM(CASE WHEN Condition = 2 THEN 1 ELSE 0 END)>0 AND
SUM(CASE WHEN Condition = 3 THEN 1 ELSE 0 END)>0

Hope this helps

sica
Posting Yak Master

143 Posts

Posted - 2001-12-07 : 11:49:47

You can try to do like this :

SELECT * FROM #temp
WHERE
CASE WHEN (SELECT COUNT(*) FROM #temp WHERE Location IN (1,2,6)) = 3 THEN 1 ELSE 0 END = 1

but you have to know how many numbers you compare the count with.In this exemple is 1,2,6 ,so 3 nbr.

Sica

Edited by - sica on 12/07/2001 11:50:21

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources