mySql - creating a join using a list of comma separated values

Question

I've got a table with a field for Merchant's name and field with the Services they provide. The Services field is a comma separated list of integers that relate to another Services table, with the Service id and the Service Name fields.

I'm trying to create a single query that joins those two, so I can have a list of Merchants, along with the Services Names. My solution so far has been to do a second loop within my initial 'foreach' loop, but that can mean 5 or 6 db calls for each Merchant name.

After some StackOverflow-ing (google-ing), I noticed that using a comma separated field is probably not the best way to go.

Anyone have either a way to do the join, or thoughts on how the db structure could be set up better? Many thanks in advance!

Marcus Adams · Accepted Answer · 2010-06-25 20:48:26Z

6

Merchant
MerchantId   Name
          1   Adams Consulting

Merchant_Services
MerchantId    Service
         1    SEO
         1    Brand Consulting

You can actually get a comma separated list back:

SELECT m.*, GROUP_CONCAT(ms.Service) AS Services
FROM Merchant m
LEFT JOIN Merchant_Serivces ms
ON ms.MerchantId = m.MerchantId
GROUP BY m.MerchantId
ORDER BY m.Name, ms.Service

Results in:

MerchantID  Name              Services
----------  ----------------  --------------------
         1  Adams Consulting  Brand Consulting,SEO

edited Jun 25, 2010 at 20:48

answered Jun 25, 2010 at 18:51

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

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

2 Comments

TerryMatula Over a year ago

This is perfect. Thanks! I've got a third table with just a list of Services, but that should be a fairly simple join.

Devner Over a year ago

@Marcus Adams The solution is perfect for the specified case. What if we need a space after every comma?

OMG Ponies · Accepted Answer · 2010-06-25 18:46:38Z

6

The short term solution to your issue is to use the FIND_IN_SET function to join the MERCHANT and SERVICES tables:

SELECT *
  FROM MERCHANT m
  JOIN SERVICES s ON FIND_IN_SET(s.service_id, m.services) > 0

The long term solution is to correct your tables - never allow columns to contain comma separated lists of referential ID/etc values.

answered Jun 25, 2010 at 18:46

community wiki

OMG Ponies

Collectives™ on Stack Overflow

mySql - creating a join using a list of comma separated values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related