Mysql – Retrieve parent row when key appears in either one of another two tables

MySQL

My actual data and sql is more complicated, but I've stripped it down to the following. I have 3 tables.

match    
+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
| 3  | match 3 |
| 4  | match 4 |
+----+---------+

home_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 1        | Angus  |
| 2  | 1        | Bertie |
| 3  | 1        | Carl   |
| 4  | 1        | Dave   |
+----+----------+--------+

away_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 2        | Angus  |
| 2  | 2        | Bertie |
| 3  | 2        | Carl   |
| 4  | 2        | Dave   |
+----+----------+--------+

I want to know the match.id and match.title for all matches that Dave has played in, so expecting two rows:

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
+----+---------+

I've tried the following:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
INNER JOIN away_players a ON m.id = a.match_id
WHERE h.player = 'Dave' OR a.player = 'Dave'

But this only ever returns "match 1" and misses "match 2", which Dave has also played in.

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
+----+---------+

It's actually returning a number of rows, but the DISTINCT sorts that out.

What's the sql I need here?

Best Answer

Probably the most natural way to do this for most people to do this is with an IN statement and since you want where he is on either the Home Or the Away team the whole thing looks like this.

SELECT m.id, m.title
FROM match m
WHERE m.id IN (SELECT h.match_id FROM home_players h where h.player = 'Dave')
OR m.id IN (SELECT a.match_id FROM away_players a where a.player = 'Dave')

Another way to do this would be to do LEFT joins. You can't use an inner join because you are only left with the entries on the Match table that exist in the table.

SELECT DISTINCT m.id, m.title
FROM match m
LEFT OUTER JOIN home_players h ON m.id = h.match_id and h.player = 'Dave'
LEFT OUTER JOIN away_players a ON m.id = a.match_id and a.player = 'Dave'
WHERE H.id is not NULL or a.id is not NULL

The LEFT OUTER JOINs keep all the entries in the Match table and just fill in NULLs when Dave wasn't part of one of the games and you can look for places where one or the other isn't null.

You could also do this using a Union to build a list of all the IDs from the two tables as a single list. This could be as part of a sub-query like used in the IN example or as a temp table or even a CTE.

Related Question