Jason Axelrod Jason Axelrod - 5 months ago 10
SQL Question

Fetching two matches in a MySQL query?

So I have some code I'm trying to figure out... I have two tables:

TABLE: matches

event_id
match_id (primary)
match_score
match_p1
match_p2
match_win


TABLE: results

event_id
user_id
result_id (primary)
result_name
result_extra


The weird thing about the data is the content of of the matches table actually links to the results table in multiple fashions.

There will be an integer in
match_p1
and
match_p2
that link to the
results_extra
field on the results table. This is designed because each match has two players in it (p1 and p2), and each player has one result for each event.

If I wanted to get a list of all matches in an event, I would do the following:

SELECT *
FROM matches
WHERE event_id = 324


If I wanted to get a list of all matches belonging to a single player, I would do:

SELECT matches.*
FROM matches
LEFT JOIN results
ON ((results.result_extra = matches.match_p1) OR
(results.result_extra = matches.match_p2))
WHERE results.user_id = 1566


However, this is where things get a bit complicated... What if I wanted to get a list of matches where player
1566
fought player
2058
? Its the logic for this query I can't figure out. Could you guys help me out?

Answer

Could be this

SELECT matches.*
FROM matches
LEFT JOIN results a
    ON ((a.result_extra = matches.match_p1  AND
         a.result_extra = matches.match_p2))
LEFT JOIN results b
    ON ((b.result_extra = matches.match_p1 AND
         b.result_extra = matches.match_p2))      
WHERE a.user_id = 2058
AND b.user_id = 1566
Comments