0

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.

4
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by mail2saion
0

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))
0

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
0

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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.