By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table cars (id int);
create table car_tags (car_id int, tag_id int);
insert into cars
values (297360),(564800),(564830),(1720948),(123456),(234567),(345678);
Records: 7 Duplicates: 0 Warnings: 0
insert into car_tags
values (297360,5),(564800,3),(564800,4),(564830,2),(345678,4),
(1720948,1),(123456,3),(123456,4),(123456,6),(234567,3);
Records: 10 Duplicates: 0 Warnings: 0
SELECT
c.id,
GROUP_CONCAT(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
GROUP BY c.id
ORDER BY c.id
| id | tag_ids |
|---|---|
| 123456 | 3,4,6 |
| 234567 | 3 |
| 297360 | 5 |
| 345678 | 4 |
| 564800 | 3,4 |
| 564830 | 2 |
| 1720948 | 1 |
SELECT
c.id,
GROUP_CONCAT(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
GROUP BY c.id
HAVING
SUM(t.tag_id = 3) > 0
AND SUM(t.tag_id = 4) > 0
ORDER BY c.id
| id | tag_ids |
|---|---|
| 123456 | 3,4,6 |
| 564800 | 3,4 |
SELECT
c.id,
GROUP_CONCAT(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
WHERE t.tag_id IN (3,4)
GROUP BY c.id
HAVING
SUM(t.tag_id = 3) > 0
AND SUM(t.tag_id = 4) > 0
ORDER BY c.id
| id | tag_ids |
|---|---|
| 123456 | 3,4 |
| 564800 | 3,4 |
SELECT
c.id,
GROUP_CONCAT(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
WHERE t.tag_id IN (3,4)
GROUP BY c.id
ORDER BY c.id
| id | tag_ids |
|---|---|
| 123456 | 3,4 |
| 234567 | 3 |
| 345678 | 4 |
| 564800 | 3,4 |