CODEWITHSUNDEEP
sql
python_crypto_questions
python_crypto_questions

Reputation: 301

SQL match multiple columns when both columns match ANY entry in a different table

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

Answers (1)

Andres Silva
Andres Silva

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

Related Questions