MYSQL left join to multiple columns

Question

I'm trying select the results of a one-to-many relation and get the results in multiple columns. My tables look like:

Table users:

+----+----------+ 
| id | user     | 
+----+----------+ 
| 1  | jenny    | 
| 2  | chris    | 
| 3  | harry    | 
+----+----------+

Table locations:

+----+-------------+ 
| id | location    | 
+----+-------------+ 
| 1  | New York    | 
| 2  | Washington  | 
| 3  | Memphis     | 
| 4  | Dallas      | 
| 5  | Las Vegas   | 
+----+-------------+

Table user_locations:

+----+---------+-------------+ 
| id | user_id | location_id |
+----+---------+-------------+
| 1  |       1 |           1 | 
| 2  |       1 |           3 | 
| 3  |       1 |           5 | 
| 4  |       2 |           2 | 
| 5  |       2 |           4 | 
| 6  |       3 |           4 | 
| 7  |       3 |           5 | 
| 8  |       3 |           2 | 
+----+---------+-------------+

The result I'm trying to get is:

+---------+-------+------------+------------+------------+ 
| user_id | user  | location_1 | location_2 | location_3 | 
+---------+-------+------------+------------+------------+ 
|       1 | jenny | New York   | Memphis    | Las Vegas  | 
|       2 | chris | Washington | Dallas     | NULL       | 
|       3 | harry | Dallas     | Las Vegas  | Washington | 
+---------+-------+------------+------------+------------+

I tried several queries with left join and group by but I seem to be getting the first location only.

Thanks in advance.

You want to achieve this with query only or a foreach loop on result is ok? — SRK, Mar 16 '18 at 07:13
First of all, you can not convert data (rows) to columns easily. You need to implementing something like pivoting. It is not that easy job. — Gaurang Dave, Mar 16 '18 at 07:13
What you are trying to do can be achieved by a loop. Just left join all 3 tables and get all the data in an array. Manipulate the array to get the desired result. — SRK, Mar 16 '18 at 07:17
Seriously consider handling issues of data display in application code — Strawberry, Mar 16 '18 at 07:42
If a user has more then 3 locations the result only need the top 3 locations. — Erwin van Hoof, Mar 16 '18 at 08:30

score 1 · Answer 1 · answered Mar 16 '18 at 07:23

The data structure and orientation change is generally referred as Transpose. In this Transpose if you wish to add dynamic values then it is called as PIVOT.

See if this helps: Transpose MySQL rows as column names - DYNAMIC https://dba.stackexchange.com/questions/89681/transpose-mysql-rows-as-column-names-dynamic

MySQL pivot table: MySQL pivot table

Gaurang Dave · Answer 2 · 2018-03-16T07:56:35.817

I tried to write to this StoredPorcedure for you as fast as I can. It will convert rows to columns.

Still you need to manage nulls in this SP but I am sure it will give you better idea to achieve you requirement.

check this FIDDLE

CODE

DELIMITER $$
CREATE PROCEDURE sp_Result()
BEGIN
    SET SESSION group_concat_max_len = 500000;

    SET @sql = NULL;

    SELECT GROUP_CONCAT(DISTINCT
         CONCAT(
           'MAX(CASE WHEN ul.location_id = ', l.id, 
           ' THEN l.location END) '))

      INTO @sql
      FROM user_locations ul 
      INNER JOIN locations l ON ul.location_id = l.id;

    SET @sql = CONCAT(
                 'SELECT ul.user_id, u.user, ', @sql,  
                  ' FROM user_locations ul INNER JOIN users u
                      ON ul.user_id = u.id INNER JOIN locations l ON ul.location_id = l.id GROUP BY ul.user_id, u.user;');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

P.S.

Do modifications in it as per you need as it will not return you exact result you want.

MYSQL left join to multiple columns

2 Answers2