MySQL: How to filter out groups that don't contain a specific value?

Question

I want to filter out all request_id's, that belong to sessions that don't contain site_id 2. So if a user didn't visit site 2, the session is irrelevant to me.

request_id   session_id   site_id
1            1            1
2            1            2
3            1            3
4            2            1
5            2            3
6            2            3

Result:

request_id   session_id   site_id
1            1            1
2            1            2
3            1            3

Right know I could think of something like that (didn't check if working):

SELECT
session_id,
GROUP_CONCAT(site_id order by request_id asc SEPARATOR ', ') as site_ids
FROM requests
GROUP BY session_id
HAVING site_id LIKE ('% 2, %' OR '2, %')
ORDER BY session_id asc;

But I also need the related session_ids not aggregated in one cell. So is it possible to avoid the GROUP BY session_id that generates the output?

Do you have better and more efficient solutions?

Answer 1

I would just do a sub query to check if the session_id has a request to site_id = 2.

SELECT
session_id,
GROUP_CONCAT(site_id order by request_id asc SEPARATOR ', ') as site_ids
FROM requests r
WHERE EXISTS(SELECT NULL FROM requests WHERE session_id = r.session_id AND site_id = 2)
GROUP BY session_id
ORDER BY session_id asc;

Answer 2

I think you might need to use a sub-select to achieve the desired outcome.

SELECT
request_id, session_id, site_id
FROM requests
WHERE session_id in (
  select distinct session_id FROM requests where site_id=2)
ORDER BY session_id asc;

Or a join

SELECT
A.request_id, A.session_id, A.site_id
FROM requests A
RIGHT JOIN requests B ON
   A.session_id = B.session_id AND B.site_id=2
GROUP BY A.session_id
ORDER BY A.session_id asc;