June 27, 2008 at 7:51 am
I have a table A with column ID and table B with columns ID and PID.
Now I need to see if there exists one or more PID with the exact same ID`s in table B as all the ID`s in table A.
Example
Table A
ID
51
78
34
Table B
IDPID
204
514
784
344
515
785
345
In this example PID 5 exists with the exact same combination of ID`s in A and B. PID 4 includes all the ID`s from A, but it does also have the ID 20, so it`s not included.
Can someone give me some help with the SQL-statement?
June 27, 2008 at 10:16 am
Select distinct PID
From tableb b
where pid not in (
select distinct PID
from tableb b
left join tablea a
on a.id = b.id
where a.id is null
)
June 27, 2008 at 1:00 pm
Alternatively:
Create Table #A (ID int)
Insert Into #A Values (51)
Insert Into #A Values (78)
Insert Into #A Values (34)
Create Table #B (ID int, PID int)
Insert Into #B Values(20,4)
Insert Into #B Values(51,4)
Insert Into #B Values(78,4)
Insert Into #B Values(34,4)
Insert Into #B Values(51,5)
Insert Into #B Values(78,5)
Insert Into #B Values(34,5)
Insert Into #B Values(51,3)
Insert Into #B Values(78,3)
Select PID
From #B
Left Join #A
On (#A.ID = #B.ID)
Group By PID
Having Count(*) = (Select Count(*) from #A)
June 27, 2008 at 4:39 pm
Thank you for quick feedback! I don`t have time to test it at this moment, but it looks good.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply