11

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?

gen_Eric
  • 223,194
  • 41
  • 299
  • 337

1 Answers1

26

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%'
gen_Eric
  • 223,194
  • 41
  • 299
  • 337
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Wow, that actually worked! I guess there's no cleaner way to do this? – gen_Eric Sep 27 '10 at 19:02
  • 1
    'cleaner' is debatable, but you could use a `HAVING` clause instead of a subquery I believe, or a separate extra `JOIN` on contacts with the `LIKE` in the `ON` clause, but I'd test which one is better / performs better. – Wrikken Sep 27 '10 at 19:06
  • I'm not sure how to use `HAVING`, and the query I posted is only part of the full one, there are 3 more `JOIN` statements in there. – gen_Eric Sep 27 '10 at 19:09
  • I tried adding `LIKE' to the 'JOIN` statement's `ON` clause. It returned the correct records but only showed one name in the `attachedContacts` field, not all of them. – gen_Eric Sep 27 '10 at 19:12
  • I went back into this code, and using `HAVING` is better than using subqueries. – gen_Eric Nov 04 '10 at 13:40