Reputation: 150
I have two tables; A and B. Table A contains a column of GUIDs. Table B contains a column of GUIDs and a column of ClientIDs. In table B, the ClientIDs may be matched to multiple GUIDs. If given 1 GUID, how would I select all GUIDs from table A that belong to the same ClientID as the given GUID?
For Example, if Table A has three rows:
Row1 | 11111111-2222-3333-4444-555555555555
Row2 | 22222222-3333-4444-5555-666666666666
Row3 | 33333333-4444-5555-6666-777777777777
Table B has 3 rows:
Row1 | ClientA | 11111111-2222-3333-4444-555555555555
Row2 | ClientB | 22222222-3333-4444-5555-666666666666
Row3 | ClientA | 33333333-4444-5555-6666-777777777777
I am given a GUID (11111111-2222-3333-4444-555555555555
), how would I write a select statement to find all GUIDs from A that match the ClientID from Table B? (11111111-2222-3333-4444-555555555555
& 33333333-4444-5555-6666-777777777777
)
Upvotes: 0
Views: 232
Reputation: 57073
SELECT a_guid
FROM TableA
INTERSECT
SELECT a_guid
FROM TableB
WHERE ClientID IN ( SELECT ClientID
FROM TableB
WHERE a_guid = @given_guid );
Upvotes: 0
Reputation: 13765
Eh, how about first finding the client with the given guid from table b
SELECT client FROM table_b WHERE guid = <given_guid>
and then getting all guid's for that client from table b
SELECT guid FROM table_b WHERE client = <client>
I mean you could do it in one go using subqueries
SELECT guid FROM table_b WHERE client IN (
SELECT client FROM table_b WHERE guid = <given_guid>
)
but why complicate things
If you need additional data from table a for the guid's for clients, you can either JOIN
or use the above as another subquery, e.g.
SELECT guid FROM table_a WHERE guid IN (
SELECT guid FROM table_b WHERE client IN (
SELECT client FROM table_b WHERE guid = <given_guid>
)
)
p.s. I hate using subqueries, there almost always is a better way
Upvotes: 1