query help

[ramanaths]

I have the following data in a table named dtlstbl which has columns id and refid

100,1
100,2
101,1
102,2
103,1
103,2

I am fetching data from this table thus
select id from dtlstbl where refid in (1,2)

All the records are returned.

I want a SQL 2000 query that will return only the following result
100
103

ie. id's which have ref id both 1 and 2.

[Ole Raptor]

there may be another way but off the top of my head you can use "or"

SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2

actually i just did a quick search and found

SELECT id FROM dtlstbl WHERE (refid(1,2))

[pty]

I think a self join is required:

select distinct 
    d1.id 
from dtlstbl as d1 
inner join dtlstbl as d2 
    on d1.id = d2.id 
    and d2.refid = 2 
where
    d1.refid = 1;

there may be another way but off the top of my head you can use "or"

SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2

[mail2saion]

SINCE YOUR REF ID IS FIXED SO NO NEED TO SELF JOIN IN THIS SCENARIO. JUST TAKE THE GROUPWISE COUNT =2. Note if refid duplicated for a specific id then below query will not work. In this scenario follow the 2nd query

SELECT ID FROM dtlstbl
GROUP BY ID
HAVING COUNT(*)=2

GENERIC QUERY

SELECT DISTINCT ID FROM(
SELECT t1.ID ID,t1.REFID ref1,t2.refid ref2 FROM dtlstbl t1 
LEFT JOIN dtlstbl t2 ON t1.id=t2.id) tbl WHERE tbl.ref1=1 AND tbl.ref2=2