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