Josef Fernan Penales Josef Fernan Penales - 1 year ago 68
SQL Question

SQL Sub-Query and Table Joins Issue

Hi I need a help regarding this problem. I tried subqueries but I did not get the results I want.

Theses are my tables:

ID Desc
1 Round
2 Smooth
3 Rough

ID Name
1 apple
2 orange

ID fruit_id fruit_desc
1 1 1
2 2 2
3 2 3

I want the output :

Name Desc
Apple Round
Orange Smooth,Rough

Answer Source

Join the three tables together through tblmultidesc, and use GROUP_CONCAT to get a comma-separated list of descriptions for each fruit.

SELECT t3.Name,
       GROUP_CONCAT(t2.Desc) AS Desc
FROM tblmultidesc t1
INNER JOIN tblefruitdesc t2
    ON t1.fruit_desc = t2.ID
INNER JOIN tblefruit t3
    ON t1.fruit_id = t3.ID
GROUP BY t3.Name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download