CODEWITHSUNDEEP
mysqlsql
Flyingdiver
Flyingdiver

Reputation: 2142

Alternative to GROUP_CONCAT? Multiple joins to same table, different columns

I'm not even sure of the correct terminology here. MySQL newbie, more or less.

Given a couple tables defined as follows:

    CREATE TABLE users 
 ( user_id int(11) NOT NULL auto_increment
 , name VARCHAR(255)
 , pri_location_id mediumint(8)
 , sec_location_id mediumint(8)
 , PRIMARY KEY  (user_id)
 );

 CREATE TABLE locations 
 ( location_id mediumint(8) NOT NULL AUTO_INCREMENT
 , name varchar(255)
 , PRIMARY KEY (location_id)
 )

I'm trying to do a query to get the user name and both primary and secondary locations in one go.

I can get one like this:

SELECT u.name AS user_name, l.name as primary_location FROM users u, locations l WHERE u.primary_location_id=l.location_id

But I'm drawing a total blank on the correct syntax to use to get both in one query.

Upvotes: 0

Views: 327

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

First of, I would strongly consider changing your DB schema if allowable to add a users_locations table that can be used to properly describe this many to many relationship.

This table could look like:

user_id    location_id    location_type
1          1              primary
1          2              secondary
2          1              secondary
2          2              primary

and so forth.

You would likely want a compound primary key across all three columns. And location_type might best be enum data type.

Your query would then be like

SELECT
    u.name AS user_name
    l.name AS location_name
    ul.location_type AS location_type
FROM users AS u
INNER JOIN user_location AS ul /* possibly use left join here if user can exist without a location */
  ON u.user_id = ul.user_id
INNER JOIN locations AS l
  ON ul.location_id = l.location_id
ORDER BY ul.location_type ASC

This would return up to two records per user (separate record for primary and secondary, primary listed first)

If you need this collapsed to a single record you could do this:

SELECT
    u.name AS user_name
    COALESCE(CASE WHEN ul.location_type = 'primary' THEN l.name ELSE NULL END CASE) AS primary_location,
    COALESCE(CASE WHEN ul.location_type = 'secondary' THEN l.name ELSE NULL END CASE) AS secondary_location
FROM users AS u
INNER JOIN user_location AS ul /* possibly use left join here if user can exist without a location */
  ON u.user_id = ul.user_id
INNER JOIN locations AS l
  ON ul.location_id = l.location_id
GROUP BY `user_name`

If however you are stuck with current schema, then solution by @Jlil should work for you.

Upvotes: -1

Jlil
Jlil

Reputation: 170

SELECT u.name AS user_name, l1.name as primary_location , l2.name as secondary_location
FROM users u
JOIN locations l1 ON(u.pri_location_id=l1.location_id)
JOIN locations l2 ON(u.sec_location_id = l2.location_id);

Upvotes: 2

Related Questions