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.

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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;