CODEWITHSUNDEEP
mysqlsql
Mohammad Mehdi Habibi
Mohammad Mehdi Habibi

Reputation: 1623

how to left join 3 tables in mysql

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!

Upvotes: 0

Views: 869

Answers (3)

Suleman khan
Suleman khan

Reputation: 1068

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

Upvotes: 0

v0ld3m0rt
v0ld3m0rt

Reputation: 904

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

Upvotes: 0

Krzysztof
Krzysztof

Reputation: 16150

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

Upvotes: 2

Related Questions