Select, join two tables on a group

  • 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?

  • 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

    )

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

  • 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