Search for GROUP_CONCAT using LIKE

Question

I have an SQL query that uses GROUP_CONCAT to connect all people to a specific order. Is there any way to search in 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 list only orders with “Eric” attached, but still include all other contacts in the request.

The request returns data of the type:

 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 lines 01 and 03 because "Eric" is in the contact list.

How can i do this?

Answer 1

Try the following:

 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%'