MS SQL

MS SQL RSS

query help

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2009

Posts: 1

Reputation: ramanaths is an unknown quantity at this point

Solved Threads: 0

ramanaths

Offline

Newbie Poster

query help

May 29th, 2009

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.

•

Join Date: Dec 2007

Posts: 252

Reputation: ProfessorPC is an unknown quantity at this point

Solved Threads: 27

ProfessorPC ProfessorPC is offline

Offline

Posting Whiz in Training

Re: query help

May 29th, 2009

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2
SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2

actually i just did a quick search and found

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT id FROM dtlstbl WHERE (refid(1,2))
SELECT id FROM dtlstbl WHERE (refid(1,2))

Last edited by ProfessorPC; May 29th, 2009 at 1:02 pm.

•

Join Date: Oct 2005

Posts: 521

Reputation: pty is on a distinguished road

Solved Threads: 37

pty

Offline

Posting Pro

Re: query help

May 29th, 2009

I think a self join is required:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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;
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;

•

Originally Posted by ProfessorPC

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

sql Syntax (Toggle Plain Text)
SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2
SELECT id FROM dtlstbl WHERE refid = 1 OR refid = 2

Last edited by pty; May 29th, 2009 at 12:50 pm. Reason: Stray quote tag!

Note to self... pocket cup

•

Join Date: Apr 2009

Posts: 217

Reputation: mail2saion is an unknown quantity at this point

Solved Threads: 31

mail2saion mail2saion is offline

Offline

Posting Whiz in Training

Re: query help

Jun 1st, 2009

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT ID FROM dtlstbl
GROUP BY ID
HAVING COUNT(*)=2
SELECT ID FROM dtlstbl
GROUP BY ID
HAVING COUNT(*)=2

GENERIC QUERY

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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