CODEWITHSUNDEEP
mysqlsqldatabasejoinsubquery
Shir Gans
Shir Gans

Reputation: 2027

How to find to "opposite" records on MySQL?

I have 1 table with the following cols:

giver_id | receiver_id
   10    |     12
    9    |     10
   10    |     20
   12    |     10

I am looking for a mysql query that will return 10-12 / 12-10 as a match.

Thanks

Upvotes: 0

Views: 90

Answers (1)

GMB
GMB

Reputation: 222442

To identify the records for which an "opposite" record exist, you could do:

SELECT * 
FROM mytable t
WHERE EXISTS (
    SELECT 1 
    FROM mytable t1 
    WHERE t1.giver_id = t.receiver_id AND t.giver_id = t1.receiver_id 
)

This demo on DB Fiddle with your sample data returns:

| giver_id | receiver_id |
| -------- | ----------- |
| 10       | 12          |
| 12       | 10          |

Upvotes: 4

Related Questions