Combining multiple mySQL rows without duplicates

Question

I have 4 tables in a MySQL database, suppliers, categories, subcats & listings.

listings is a join table to allow the many to many relationships between suppliers, categories and subcats, the structure of each is as follows

suppliers

sp_id      sp_name       sp_email
1          Apple         [email protected]
2          Samsung       [email protected]

categories

cat_id     cat_name
3          Electronics
4          Software

subcats

subcat_id  subcat_name   cat_id
5          Mobiles       3
6          Computers     3

listings

list_id    sp_id         subcat_id
1          1             5
2          1             6

I am trying to combine and extract the data together so there is only one entry per supplier with multiple subcategories listed eg:

RESULT

sp_id      sp_name       sp_email         cat_name / cats        subcat_name / subcats
1          Apple         [email protected]    Electronics, Software  Mobiles, Computers
2          Samsung       [email protected]  Electronics            Mobiles

Currently I have the following query

SELECT *
FROM suppliers as s
LEFT JOIN listings as l ON s.sp_id=l.sp_id
LEFT JOIN subcats as p ON p.subcat_id=l.subcat_id
LEFT JOIN categories as c ON c.cat_id=p.cat_id
ORDER BY s.sp_id

However this outputs multiple entries per supplier, one entry for each category or subcategory associated with it. Is there an easier way to do it via SQL or with multiple queries in PHP?

I am at the limits of my current knowledge of mySQL and any suggestions or prods in the right direction would be greatly appreciated.

John Woo · Accepted Answer · 2013-02-21 00:36:47Z

1

use GROUP_CONCAT

SELECT  s.*, 
        GROUP_CONCAT(c.cat_name) catName,
        GROUP_CONCAT(p.subcat_name) subcatName
FROM    suppliers as s
        LEFT JOIN listings as l ON s.sp_id=l.sp_id
        LEFT JOIN subcats as p ON p.subcat_id=l.subcat_id
        LEFT JOIN categories as c ON c.cat_id=p.cat_id
GROUP   BY s.sp_id, s.sp_name, s.sp_email
ORDER   BY s.sp_id

answered Feb 21, 2013 at 0:36

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

silkfire · Accepted Answer · 2013-02-21 00:35:42Z

0

You can use GROUP BY in combination with GROUP_CONCAT to only show unique values from a certain column with a comma-separated list as one of the values.

answered Feb 21, 2013 at 0:35

silkfire

26.3k17 gold badges92 silver badges116 bronze badges

Comments

Andrew Miner · Accepted Answer · 2013-02-21 00:42:02Z

0

You want to use the GROUP BY expression along with the GROUP_CONCAT function. This will allow you to merge together rows which share certain values while concatenating the ones they don't. For example, in your case this may wind up being something like:

SELECT s.sp_id, s.sp_name, group_concat(', ', c.cat_name) FROM suppliers as s LEFT JOIN listings as l ON s.sp_id=l.sp_id LEFT JOIN subcats as p ON p.subcat_id=l.subcat_id LEFT JOIN categories as c ON c.cat_id=p.cat_id ORDER BY s.sp_id GROUP BY s.sp_id, s.sp_name;

answered Feb 21, 2013 at 0:42

Andrew Miner

6,2232 gold badges25 silver badges26 bronze badges

Collectives™ on Stack Overflow

Combining multiple mySQL rows without duplicates

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related