MySql - creating a connection using a comma-separated list of values

Question

I have a table with a field for the name and a Merchant field with the services they provide. The Services field is a comma-separated list of integers that refer to another Services table, with the identifiers Service and Service Name.

I am trying to create a single query that combines the two, so I can have a list of Merchants along with the service names. My solution so far has been to make a second loop in my original foreach loop, but that could mean 5 or 6 db calls for each Merchant name.

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

Anyone have a way to make a connection or thoughts on how the db structure could be tuned? Thank you very much in advance!

Answer 1

Merchant
MerchantId   Name
          1   Adams Consulting

Merchant_Services
MerchantId    Service
         1    SEO
         1    Brand Consulting

In fact, you can get a comma separated list:

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

Answer 2

A short-term solution to your problem 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 fix your tables - never let columns contain comma lists of ID / etc reference values.