MYSQL Multiple Join Query to Display Unique Column Values in One Field for Single Record

join;MySQLsubquery

I am joining together three tables in order to display the names of recommended providers for a property.

First I join my properties (alias p) table to the properties_recommended_providers table 

LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id

I use a LEFT join to still get records of properties that don't have any recommended providers and wouldn't be in the properties_recommended_providers table.

Then I join with the recommended_providers table to get the names of the providers

LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id

In the select clause I'm pulling info about each property (name, id, street, city, etc). Lastly, I want to list all the names of the recommended providers for each property in a final column which rp.name.

My issue is that before selecting rp.name I was getting distinct records for each record and now adding in rp.name I'm getting multiple records for each property that have more than one recommended provider. The multiple property records contain identical information in every column except the rp.name.

I know it's possible to concat together values from separate columns in a row and display that in a new column in a select query but in this particular case is it possible for any property with more than one recommended provider to have all those providers appear in one field together (separated by commas) for a single record for that property instead of having that property listed multiple times just to display each unique recommended provider ?

Best Answer

Following what's already written at How to use GROUP_CONCAT in a CONCAT in mysql...

Let's assume you have a schema and sample data equivalent to:

CREATE TABLE properties
(
    id integer PRIMARY KEY,
    property varchar(255) NOT NULL
) ;

CREATE TABLE recommended_providers
(
   id integer PRIMARY KEY,
   recommended_provider_name varchar(255) NOT NULL
) ;

CREATE TABLE properties_recommended_providers
(
   prop_id INTEGER NOT NULL REFERENCES properties(id),
   recommended_provider_id INTEGER NOT NULL REFERENCES providers(id),
   PRIMARY KEY (prop_id, recommended_provider_id)
) ;

INSERT INTO properties
VALUES (1, 'property 1'), (2, 'property 2') ;

INSERT INTO recommended_providers
VALUES (1, 'provider 1'), (2, 'provider 2'), (3, 'provider 3');

INSERT INTO properties_recommended_providers
VALUES (1, 1), (1, 2), (2, 3) ;

You can either have:

SELECT
  p.id AS property_id, property, 
  GROUP_CONCAT(recommended_provider_name SEPARATOR ', ') AS provider_names
FROM
  properties p
  LEFT JOIN properties_recommended_providers pr ON pr.prop_id = p.id
  LEFT JOIN recommended_providers rp ON rp.id = pr.recommended_provider_id
GROUP BY
  property_id
ORDER BY
  property_id ;

(Check it at SQL Fiddle)

or 

SELECT
  p.id AS property_id, property, 
  (SELECT
      GROUP_CONCAT(recommended_provider_name SEPARATOR ', ')  
   FROM
       properties_recommended_providers pr
       LEFT JOIN recommended_providers rp ON rp.id =
                                         pr.recommended_provider_id
   WHERE
       pr.prop_id = p.id
   ORDER BY
       /* You can easily order by in this case */
       recommended_provider_name
   ) AS provider_names
FROM
  properties p
ORDER BY
  property_id ;

You can check the second one at SQL Fiddle

Related Question