1

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?

1

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.

| improve this answer | |
  • Marked the second suggestion of this one as my preferred solution, just because the query is easier to build in the Drupal 7 query builder I'm developing in. I'll add that the DISTINCT doesn't seem to be required. Appreciative of all answers. – Dunx Aug 25 '16 at 23:31
1

A good way to do that is likely to use the UNION of two queries, one for home_players, and one for away_players. Something like:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
WHERE h.player = 'Dave'
UNION 
SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN away_players a ON m.id = a.match_id
WHERE a.player = 'Dave'
| improve this answer | |
  • A UNION of this kind is what I was considering too. DISTINCT is probably unnecessary since you have UNION (as opposed to UNION ALL). At first I also thought that UNION should be replaced with UNION ALL but then I remembered that sometimes (rarely I guess, perhaps only in friendlies) players do play for both sides, so UNION makes sense to eliminate potential duplicate names coming from both halves of the query. – Andriy M Aug 26 '16 at 5:08
  • @Andriy I was considering UNION ALL as well. I don't know enough about how UNION works on MySQL with regards to duplicate elimination within each set, hence I left it in - but I agree it likely isn't required to DISTINCT each set. – Max Vernon Aug 26 '16 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.