Question 113282 in Mysql Question posted in
The official MySQL documentation can be found here.

Join 6 tables and generate comma seperated values in MySQL

I have the following six tables:

distributor_master

id distributor_name distributor_status
DS-1 distributor1 NEW
DS-2 distributor2 NEW
DS-3 distributor3 UPDATED
DS-4 distributor4 NEW

dealer_master

id dealer_name dealer_status d_country_id d_state_id d_district_id
DL-1 dealer1 NEW 1 1 1
DL-2 dealer2 NEW 2 1 2
DL-3 dealer3 NEW 2 1 2
DL-4 dealer4 NEW 1 2 3

dealer_distributor_mapping

id dealer_id distributor_id
1 DL-1 DS-1
2 DL-1 DS-2
3 DL-1 DS-4
4 DL-2 DS-2
5 DL-2 DS-4
5 DL-3 DS-4
5 DL-4 DS-1

country_master

id name
1 India
2 USA

state_master

id name
1 Maharashtra
2 Delhi
3 Gujrat

district_master

id name
1 Mumbai
2 Nashik
3 Pune

I want to display the field "distributor_master.distributor_name" as a comma-seperated value, with a bunch of other information from the other tables as follows:

id dealer_name distributor_name country_name state_name district_name
DL-1 dealer1 distributor1,distributor2,distributor4 India Maharashtra Mumbai
DL-2 dealer2 distributor2,distributor4 USA Maharashtra Nashik
DL-3 dealer3 distributor4 USA Maharashtra Nashik
DL-4 dealer4 distributor1 India Delhi Pune

I have tried below query but not able to get output as needed.

SELECT dlm.id              AS id,
       dlm.dealer_name     AS dealer_name,
       dm.distributor_name AS distributor_name,
       cm.name             AS country_name,
       sm.name             AS state_name,
       dsm.name            AS district_name  
FROM dealer_master AS dlm
JOIN dealer_distributor_mapping AS ddm ON dlm.id = ddm.delaer_id 
JOIN distributor_master AS dm          ON ddm.distributor_id = dm.id
JOIN country_master as cm              ON dlm.d_country_id = cm.id
JOIN state_master as sm                ON dlm.d_state_id = sm.id
JOIN district_master as dsm            ON dlm.d_district_id = dsm.id
WHERE dlm.dealer_status = 'NEW';

If anyone have idea how to do this please let me know.

Tags:

3

Answers


  1. 0

    Could you try this one ,

    SELECT dealer_name, distributor_name, country_master.name as country_name, state_master.name as state_name, district_master.name as district_name FROM dealer_master
LEFT JOIN dealer_distributor_mapping ON dealer_distributor_mapping.dealer_id = dealer_master.id
LEFT JOIN distributor_master ON distributor_master.id = dealer_distributor_mapping.distributor_id
LEFT JOIN country_master ON country_master.id = dealer_master.d_country_id
LEFT JOIN state_master ON state_master.id = dealer_master.d_state_id
LEFT JOIN district_master ON district_master.id = dealer_master.d_district_id
WHERE dealer_master.dealer_status = 'NEW';

    You can use INNER JOIN if you absolutely need to match all data.

    Login or Signup to reply.
  2. 0

    You can use the GROUP_CONCAT aggregate function on the distributor names, to generate your comma-separated field. Then join back to the other tables with respect to the corresponding matching ids.

    WITH csv_mapping AS (
    SELECT ddm.dealer_id,
           GROUP_CONCAT(dm.distributor_name) AS distributor_name
    FROM       dealer_distributor_mapping ddm
    INNER JOIN distributor_master dm ON ddm.distributor_id = dm.id
    GROUP BY ddm.dealer_id
)
SELECT dlm.id,
       dlm.dealer_name,
       m.distributor_name,
       cm.name AS country_name,
       sm.name AS state_name,
       dm.name AS district_name
FROM       dealer_master   dlm
INNER JOIN csv_mapping       m ON dlm.id            = m.dealer_id
INNER JOIN country_master   cm ON dlm.d_country_id  = cm.id
INNER JOIN state_master     sm ON dlm.d_state_id    = sm.id
INNER JOIN district_master  dm ON dlm.d_district_id = dm.id
WHERE dlm.dealer_status = 'NEW'

    Check the demo here.

    Login or Signup to reply.
  3. 0

    Complementing @lemon ‘s answer, in case you are in versions previous to MySQL 8.0 (And can’t use CTEs), you would have to add every column to the GROUP BY clause that is not affected by an aggregate function.

    SELECT
  dlm.id as id,
  dlm.dealer_name,
  group_concat(dm.distributor_name),
  cm.name as country_name,
  sm.name as state_name,
  dsm.name as district_name
FROM dealer_master dlm
JOIN dealer_distributor_mapping AS ddm ON dlm.id = ddm.delaer_id 
JOIN distributor_master AS dm          ON ddm.distributor_id = dm.id
JOIN country_master as cm              ON dlm.d_country_id = cm.id
JOIN state_master as sm                ON dlm.d_state_id = sm.id
JOIN district_master as dsm            ON dlm.d_district_id = dsm.id
WHERE dlm.dealer_status = 'NEW'
group by dlm.id, dlm.dealer_name, cm.name, sm.name, dsm.name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search