CODEWITHSUNDEEP
mysql
Bruno
Bruno

Reputation: 95

Getting matching records from mysql query

I have database table with the following structure, that is used to match people at a dating event:

ID | EVENT | USERS_ID | MATCH_ID | GENDER
1 | 27 | 54 | 1245 | Female
2 | 27 | 54 | 21 | Female
3 | 27 | 21 | 45 | Male
4 | 27 | 21 | 54 | Male

I am trying to write my query that will give me my matches. For example, from the data above, user 54 matches user 21 because 54 picked 21 and 21 picked 54.

I need to pass as an input a USER_ID and the EVENT and get a list of the MATCH_ID that match that user. So, 

SELECT match_id FROM……. WHERE users_id = 54 AND event = 27

I also need to know which users DIDN’T get any matches. This would be a separate query. From the example above, user 45 had no mutual matches.

I’ve been fighting with this one for a while. Any ideas on this would be great.

Thanks.

Upvotes: 0

Views: 111

Answers (1)

user470714
user470714

Reputation: 2888

Not tested, but I think this should do it for you:

SELECT t2.users_id FROM 
tbl t1
INNER JOIN tbl t2
ON t1.users_id = t2.match_id AND t1.match_id = t2.users_id
WHERE t2.event = 27
AND t1.users_id = 54;

Also, for finding all the users who don't have any matches, this would probably work: 

SELECT t1.match_id
FROM
tbl t1
LEFT JOIN 
tbl t2
ON t1.match_id = t2.users_id
WHERE t2.id IS NULL;

The general principle here though, is to join the table to itself, and find matches that way.

EDIT: Made some corrections, feel pretty confident this should work well for you.

EDIT 2: Here is the final correction, which I believe should pull this all together for you.

SELECT * FROM event_matches t1 INNER JOIN event_matches t2 ON t1.users_id = t2.matches AND t1.matches = t2.users_id AND t1.event_id = t2.event_id WHERE t2.event_id = 45 AND t1.users_id = 1239;

Upvotes: 2

Related Questions