Reputation: 301
I have two tables (below). I am trying to return the entry of TABLE B
when BOTH columns match ANY of the items in TABLE A
.
In the tables below, I'd like to return the following,
Expected Result
from | to |
---|---|
userB | userA |
userC | userB |
TABLE A
name |
---|
userA |
userB |
userC |
TABLE B
from | to |
---|---|
userA | x |
userB | userA |
y | userC |
z | t |
userC | userB |
Upvotes: 0
Views: 67
Reputation: 892
Try this:
SELECT T1.*
FROM TableB AS T1
INNER JOIN TableA AS T2
ON T1.from = T2.name
INNER JOIN TableA AS T3
ON T1.to = T3.name
The idea is that you first get every record from TableB where it's first column (from) matches any record from TableA.
Then, you filter that result by those records from TableB where it's second column (to) matches any record from TableA.
EDIT
Another approach could be:
Select *
from TableB AS T1
where T1.from in (select distinct name from TableA)
and T1.to in (select distinct name from TableA)
But I'm not sure that would be more efficient.
It would be even better if you did a with
statement instead of (select distinct name from TableA)
.
Upvotes: 1