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.
3
Answers
Could you try this one ,
You can use INNER JOIN if you absolutely need to match all data.
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.Check the demo here.
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.