how to left join 3 tables in mysql

Question

i have 3 tables:

members(nic,name)

awards(nic,aw_name)

championships(nic,ch_name)

a person can have multiple awards or championships. for example Richard has 2 awards and 3 championships, and Steve does not have any award or championship.

now i'd like to list of persons with awards and championships. for example:

|    nic   |    name    |   aw_name   |      ch_name      |
|----------|------------|-------------|-------------------|
| 1        | Richard    | award 1     | championship 1    |
| 1        | Richard    | award 2     | championship 2    |
| 1        | Richard    |             | championship 3    |
| 2        | Steve      |             |                   |

Can anyone help me out? Thanks!

http://stackoverflow.com/questions/9853586/sql-join-multiple-tables — Sergey N Lukin, Nov 18 '13 at 09:03

Krzysztof · Accepted Answer · 2013-11-18T09:56:59.373

Try this:

SELECT
    members.nic,
    members.name,
    awards.aw_name,
    championships.ch_name
FROM
    (
        SELECT
            CASE WHEN @curNic = members.nic THEN @curRow := @curRow + 1 ELSE @curRow := 1 END num,
            @curNic := members.nic nic,
            name
        FROM
            members LEFT JOIN
            (
                SELECT nic FROM awards UNION ALL
                SELECT nic FROM championships
            ) tmp ON tmp.nic = members.nic JOIN
            (SELECT @curNic := null, @curRow := 0) r
    ) members LEFT JOIN
    (
        SELECT
            CASE WHEN @curNic1 = nic THEN @curRow1 := @curRow1 + 1 ELSE @curRow1 := 1 END num,
            @curNic1 := nic nic,
            aw_name
        FROM
            awards JOIN
            (SELECT @curNic1 := null, @curRow1 := 0) r
    ) awards ON awards.nic = members.nic AND awards.num = members.num LEFT JOIN
    (
        SELECT
            CASE WHEN @curNic2 = nic THEN @curRow2 := @curRow2 + 1 ELSE @curRow2 := 1 END num,
            @curNic2 := nic nic,
            ch_name
        FROM
            championships JOIN
            (SELECT @curNic2 := null, @curRow2 := 0) r
    ) championships ON championships.nic = members.nic AND championships.num = members.num
WHERE
    awards.nic IS NOT NULL OR championships.nic IS NOT NULL OR members.num = 1

It would be easier if there was FULL OUTER JOIN in mysql, but there isn't.

Fiddle: http://www.sqlfiddle.com/#!2/31d47/43

but this return 6 row for Richard and repeat awards and championships — Mohammad Mehdi Habibi, Nov 18 '13 at 09:15
@MohamadMehdiHabibi It is not that easy with this schema, but see my update. — Krzysztof, Nov 18 '13 at 09:49

Suleman khan · Answer 2 · 2013-11-18T14:28:54.150

0

Try this out.

SELECT m.nic,
       m.name,
       a.aw_name,
       c.ch_name
FROM members m
LEFT JOIN awards AS a ON m.nic = a.nic
LEFT JOIN championships AS c ON m.nic = c.nic
GROUP BY m.nic, m.name

edited Nov 18 '13 at 14:28

answered Nov 18 '13 at 09:14

Suleman khan

1,038
4
14
34

v0ld3m0rt · Answer 3 · 2013-11-18T10:39:12.440

0

You can use join to merge tables according to your requirements.

SELECT mem.nic,mem.name, awd.aw_name, chm.ch_name
FROM members mem
LEFT JOIN awards AS awd ON mem.nic = awd.nic
LEFT JOIN championship AS chm ON mem.nic = chm.nic
GROUP BY mem.nic, mem.name;

For more click here: http://www.w3schools.com/sql/sql_join.asp

edited Nov 18 '13 at 10:39

answered Nov 18 '13 at 10:35

v0ld3m0rt

866
3
12
47

how to left join 3 tables in mysql

3 Answers3