mardi 28 juin 2016

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?

Aucun commentaire:

Enregistrer un commentaire