CODEWITHSUNDEEP
mysqlcodeigniterjoin
Laurence
Laurence

Reputation: 60088

Multiple joins on mySQL table

I have 2 tables. The first table holds lots of user data. The second table holds a list of classes, and if the positions have been filled.

I need to show the classes, with the user id and the user name.

I've tried doing "joins" - but the names seem to just overwrite each other, so I only get one name per row.

User table:

+----------+---------+
|  user_ID |  name   |
+----------+---------+
|        1 |   Smith | 
+----------+---------+
|        2 |   Jones |
+----------+---------+
|        3 |   Tim   |
+----------+---------+
etc

Class table:

+-------------+--------+--------+--------+---+---------+
|  class_date | Spot 1 | Spot 2 | Spot 3 | . | Spot 16 |
+-------------+--------+--------+--------+---+---------+
|  2012/1/1   |   1    |    4   |   8    | . |   5     |
+-------------+--------+--------+--------+---+---------+
|  2012/2/1   |   2    |  NULL  |   1    | . |   3     |
+-------------+--------+--------+--------+---+---------+
|  2012/3/1   |   3    |    7   | NULL   | . |  NULL   |
+-------------+--------+--------+--------+---+---------+

What I want to achieve:

+-------------+-------------+------------+--------------+---+------------+
|  class_date |   Spot 1    |   Spot 2   |    Spot 3    | . |  Spot 16   |
+-------------+-------------+------------+--------------+---+------------+
|  2012/1/1   |  1 - Smith  |  4 - Ben   |   8 - Drew   | . | 5 - Loz    |
+-------------+-------------+------------+--------------+---+------------+ 
|  2012/2/1   |  2 - Jones  |  NULL      |   1 - Smith  | . | 3 - Tim    |
+-------------+-------------+------------+--------------+---+------------+
|  2012/3/1   |  3 - Tim    |  7 - Dan   |   NULL       | . | NULL       |
+-------------+-------------+------------+--------------+---+------------+

Any help would be greatly appreciated

Upvotes: 0

Views: 84

Answers (1)

John Woo
John Woo

Reputation: 263943

You need to use LEFT JOIN for this. Try,

SELECT  a.class_date,
        CONCAT(a.Spot1, ' - ', b.name) AS `SPOT 1`,
        CONCAT(a.Spot2, ' - ', c.name) AS `SPOT 2`,
        CONCAT(a.Spot3, ' - ', d.name) AS `SPOT 3`,
        ... -- keep doing until last column (i guess up to 16)
FROM    ClassTable a
            LEFT JOIN `UserTable` b
                ON a.Spot1 = b.user_ID
            LEFT JOIN `UserTable` c
                ON a.Spot2 = c.user_ID
            LEFT JOIN `UserTable` d
                ON a.Spot3 = d.user_ID
        ... ... -- keep doing until last column (i guess up to 16)

Upvotes: 2

Related Questions