MySQL Query for list of values in a single cell

Question

I have three tables in MSQL as shown below:

customer_details:

cust_id     cust_name   Mob_No
-----------------------------------------
1           john        9999999999
2           Rex         1234567890

customer_services:

cust_id     service_id
--------------------------
1           s1
2           s2
1           s2

m_service:

service_id  service_name
----------------------------
s1             IT
s2             VAT

I need the result as below:

cust_id     cust_name   service_name
--------------------------------------------
1       John        IT, VAT
2       Rex         VAT

I tried as below:

SELECT a.cust_id,a.cust_name, c.service_name 
FROM customer_details a, customer_service b, m_service c 
WHERE a.cust_id=b.cust_id AND b.service_id=c.service_id;

But I need the service names in the same row. Any help is highly appreciable

Thanks and Regards Biswa

Don't save CSV in a column http://stackoverflow.com/questions/41304945/best-type-of-indexing-when-there-is-like-clause/41305027#41305027 http://stackoverflow.com/questions/41215624/sql-table-with-list-entry-vs-sql-table-with-a-row-for-each-entry/41215681#41215681 — e4c5, Jun 03 '17 at 08:59
Consider handling issues of data display in application code — Strawberry, Jun 03 '17 at 09:55

Tim Biegeleisen · Accepted Answer · 2017-06-03T09:11:18.570

You can use MySQL's GROUP_CONCAT() function to generate the CSV list of service names which you want in your output:

SELECT
    t1.cust_id,
    t1.cust_name,
    GROUP_CONCAT(t3.service_name) AS service_name
FROM customer_details t1
INNER JOIN customer_services t2
    ON t1.cust_id = t2.cust_id
INNER JOIN m_service t3
    ON t2.service_id = t3.service_id
GROUP BY
    t1.cust_id,
    t1.cust_name

Demo here:

MySQL Query for list of values in a single cell

1 Answers1

Rextester