Mysql Joining four tables

Question

I've been having trouble linking these tables together:

Table 1: Matches

ID	Name	Date
1	Adam vs Lance	2021-09-2021
2	Bex vs Adam vs Erica	2021-08-2021
3	Craig vs Bree	2021-07-2021
4	Danielle vs Alan	2021-06-2021
5	Erica vs Zoe vs AJ	2021-05-2021
6	Bree vs Erica	2021-04-2021
7	Bree vs Lance	2021-03-2021
8	Bree vs Lance vs Zoe	2021-02-2021

Table 2: Winners:

ID	Name	Match ID	IDNum
1	Adam	1	1
2	Bex	2	3
3	Danielle	4	7
4	Zoe	5	9
5	Erica	6	4
6	Bree	7	5
7	Bree	8	5

Table 3: Losers:

ID	Name	Match ID	IDNum
1	Lance	1	2
2	Adam	2	1
3	Erica	2	4
4	Alan	4	8
5	AJ	5	10
6	Erica	5	4
7	Bree	6	5
8	Lance	7	2
9	Lance	8	2
10	Zoe	8	9

Table 3: Draws:

ID	Name	Match ID	IDNum
1	Craig	3	6
2	Bree	3	5

Table 4: Players

ID	Name	Gender
1	Adam	M
2	Lance	M
3	Bex	F
4	Erica	F
5	Bree	F
6	Craig	M
7	Danielle	F
8	Alan	M
9	Zoe	F
10	AJ	F

The query I've been trying is to look up all matches with Bree in them and order them by date.

Table 5: Output:

Match ID
3
6
7
8

Draw: Match ID: 3
Los: Match ID: 6
Win: Match ID: 7
Win: Match ID: 8

When I try to inner join wins & losses against the Match table it works but the second I include the draws it does not return anything.

If I try just returning draws it works but then inner joining either losses or wins causes 0 results.

Can anyone help me with the code that'll work?

Query I'm trying:

SELECT Matches.ID AS MatchID, Winners.Name
    FROM Matches
            inner JOIN Draws
            ON Matches.ID = Draws.MatchID
            
            inner JOIN Winners
            ON Matches.ID = Winners.MatchID
            
            inner JOIN Losers
            ON Matches.ID = Losers.Match ID
                        
            and (Winners.winner_id_num = 5
            OR
            Losers.type_id_num = 5
            OR
            Draws.IDNum = 5
            
                )
            
            GROUP BY match_id_num;

I added some extra blank lines before the tables that where not correctly show (because of a [bug](https://meta.stackoverflow.com/questions/404376/rendered-view-requires-blank-line-before-tables-but-preview-accepts-both) on SO). — Luuk, Nov 19 '21 at 17:16
In the result from `wins & losses`, you will not see match number 3. An inner join will only show values that are in the left table AND in the right table. You need to solve this using `LEFT JOIN`s. — Luuk, Nov 19 '21 at 17:22
For more reading on this, see: https://stackoverflow.com/a/28719292/724039 — Luuk, Nov 19 '21 at 17:23
Change all `INNER JOIN` to `LEFT JOIN` (and read comment above.. ) — Luuk, Nov 19 '21 at 18:19
Hi, Luuk. I've changed that but it's pulling through other none related matches too — Mr R G, Nov 19 '21 at 20:57
It is unclear, from your question, what a "none related matche" is. You can try to [edit] your question and add this info, and maybe even create a [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0) to make this problem clear. — Luuk, Nov 20 '21 at 08:36

score 0 · Answer 1 · answered Nov 20 '21 at 21:19

I would recommend you to use UNION between the 3 tables with results and join the output with the Matches table.

SELECT r.Result, r.IDMatch FROM (
    SELECT *, 'Win' as Result FROM Winners WHERE IDNum = 5
    UNION 
    SELECT *, 'Los' as Result FROM Losers WHERE IDNum = 5
    UNION 
    SELECT *, 'Draw' as Result FROM Draws WHERE IDNum = 5
) AS r
INNER JOIN Matches AS m ON m.ID = r.IDMatch
ORDER BY m.Date DESC

The output would be:

Draw  3
Los   6
Win   7
Win   8

Mysql Joining four tables

1 Answers1