SELECT WHERE id IN GROUP_CONCAT

How do I use a WHERE or HAVING clause in combination with a GROUP_CONCAT such that the returned data will include any combination of items in the GROUP_CONCAT that contain the ID not just the single match e.g.

SELECT p.id,
p.title,
GROUP_CONCAT(a.author_id) as 'aus'
FROM publications p INNER JOIN authors a
    ON p.publication_id = a.publication_id
WHERE a.author_id = 2
GROUP BY p.id,p.title

would return

id    title     aus
1     A         1,2,3
2     B         1,2
3     C         2
4     D         2
5     E         2,3

as opposed to just 3 & 4.

I've tried various combinations of HAVING and WHERE clauses on both the a.author_id column and the GROUP_CONCAT product.

Solution

I would be tempted to use a self join of the authors table, with the 2nd join checking for author_id of 2.

Guessing a bit at your data but something like this

SELECT p.id,
        p.title,
        GROUP_CONCAT(a.author_id) as 'aus'
FROM publications p 
INNER JOIN authors a ON p.publication_id = a.publication_id
INNER JOIN authors a2 ON p.publication_id = a2.publication_id
WHERE a2.author_id = 2
GROUP BY p.id,
        p.title