Selecting matching/mutual records in MySQL

Ok, this is a little tricky to explain but can be very easy to implement.

I have a table with the following structure:

`match_id` int(69) NOT NULL auto_increment,
`event_id` int(6) NOT NULL,
`users_id` int(6) NOT NULL,
`matches`开发者_StackOverflow中文版 int(6) default NULL

so here is a sample of the data

33      11      54      1221
32      11      54      21
31      11      21      54
30      11      43      54

So, in English, this is what needs to happen: It is a personal selection chart where user1 selects user2 and vice-versa. I only need the USERS_ID where they are mutually selected. If I am looking for matches = 54, I would need to get users_id =21. Because 54 matched 21 and 21 matched 54.

Any ideas on how to write the SQL statement for this? Thanks a lot. Bruno.

---

Try this:

SELECT DISTINCT a.user_id
  FROM <YOUR_TABLE> a INNER JOIN <YOUR_TABLE> b
    ON a.matches = b.users_id
   AND b.matches = a.users_id
 WHERE a.matches = 54

---

select u1.user_id, u2.user_id
from t u1, z u2
where u1.matches = u2.user_id and u2.matches = u1.user_id

you will still get 2 rows 54, 21 and 21, 54

---

SELECT `tn1`.`users_id` from `TableName` `tn1`
JOIN `TableName` `tn2` ON `tn1`.`matches` = `tn2`.`users_id`
AND `tn2`.`users_id` = `tn1`.`matches`

where TableName - the name of your table