TSQL-Multiple condition on single column

[dbtech]

Sample query attached

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

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 three records to be displayed, I know this cannot be achived using the above statement, is there any work arround for this?.


dbtech

 

[vishnuprasad]

I u have records satisfying the condn u will get three records.

I don't understand u'r requirements.

 

[dbtech]

Sorry for not being clear, please run the script which i have posted before.

means if i were to query using Example
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"

Take this for example.
select * from #temp where location in(1,2,3)
this matches for any occurance of the location,
i need something which will do location=1 AND location=2 AND location=3

Did this answer ur query?

dbtech

 

[cbokowski]

try select * from #Temp where location in (1,2,3)

 

[dbtech]

If i used this
select * from #Temp where location in (1,2,3,6), this will return the result set, Since IN operator will be converted to OR (location=1 or location=2 or location=3 or location=6) by query analyzer. which i dont want!

I need the location be matched for all conditions,
Example
select * from #temp where location =1 and location=6 and location=3, this query should return 0 rows, since 6 is not in the temp table.






dbtech

 

[tlbroadbent]

Try this query.
[tt]
Select *
From #temp
Where Location In (1,2,3)
And (Select Count(Distinct Location)
From #temp
Where Location In (1,2,3))=3[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.