CODEWITHSUNDEEP
sql
Anonymous Creator
Anonymous Creator

Reputation: 3789

Get Records with both matching values in SQL

Table A

id1 | id2     |
---------------
2  |    3     |
4  |    5     |

Table B

groupid | parentid | uid
    ----------------
    4   | 2 (id1) |  1
    4   | 3 (id2) |  2
    6   | 2       |  3
    7   | 4 (Id1) |  4
    8   | 4 (Id1) |  5
    8   | 5 (Id2) |  6
    8   | 6       |  7

I want to fetch records where groupid should have both id1 & id2.

So in this case uid 1,2 & 5,6 should be retrieved because groupid 4 & 8 have both of them.

How to achieve this in SQL? By SQL, let's say SQL Server

Upvotes: 0

Views: 93

Answers (2)

DEEPAK LAKHOTIA
DEEPAK LAKHOTIA

Reputation: 993

This should do the job for you:

select distinct B1.uid from TableB B1
join TableB B2 on B1.groupid = B2.groupid and B1.parentid ! = B2.parentid
and (
    (B1.parentid in (select id1 from TableA) AND B2.parentid in (select id2 from TableA))
    OR
    (B2.parentid in (select id1 from TableA) AND B1.parentid in (select id2 from TableA))
    )

Test it here: http://rextester.com/KZY45975

Upvotes: 2

Asakuraa Ranger
Asakuraa Ranger

Reputation: 617

Try this query

select b.uid from TableB b inner join TableA a on (a.id1 = b.parentid) inner join TableB c on (c.parentid = a.id2 and c.uid = b.uid)

might returns what you want

Upvotes: 0

Related Questions