Rocket Hazmat Rocket Hazmat - 1 year ago 91
MySQL Question

Search GROUP_CONCAT using LIKE

I have an SQL query that uses GROUP_CONCAT to get all people attached to a certain order. Is there a way I can search inside the GROUP_CONCAT field?

SELECT orders.orderID,
GROUP_CONCAT(contacts.firstName, " ", contacts.lastName) AS attachedContacts
FROM (orders)
JOIN contacts ON orders.contactID=contacts.contactID
GROUP BY orders.orderID
ORDER BY orders.orderID DESC


I want to add something like
WHERE attachedContacts LIKE '%Eric%'
, to only list orders with 'Eric' attached, but still include all other contacts in the query.

The query returns data like:

orderID atachedContacts
01 Eric Siegel, John Smith
02 Jason Jackson, Bill O'Neil
03 Eric Siegel, Jason Jackson, Neil O'Ryan


I want the query to return rows 01 and 03 because 'Eric' is in the contact list.

How can I do this?

Answer Source

Try this:

SELECT orders.orderID, 
GROUP_CONCAT(contacts.firstName, " ", contacts.lastName) AS attachedContacts
FROM orders
JOIN contacts ON orders.contactID=contacts.contactID
GROUP BY orders.orderID DESC
HAVING attachedContacts LIKE '%Eric%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download