Question 241405 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL statement to return all rows that have multiple id's

example table

I am trying to construct a SQL query that returns all the rows (id’s) that have tag: 3 AND tag: 4 – in this case it is the single row for 564800, however I can’t seem to figure out how to make this work. The image attached shows what progress I’ve made (it’s a really simple example). I am hoping to be able to generalize this statement so that I can pass it any length of tag_ids and it will filter the list to only the applicable id’s.

This is the query so far:

SELECT cars.id, array_agg(car_tags.tag_id) AS tag_ids FROM "cars" INNER JOIN "car_tags" ON "car_tags"."car_id" = "cars"."id" GROUP BY cars.id;
Tags:

2

Answers


  1. 0

    You could use:

    SELECT c.id, ARRAY_AGG(ct.tag_id) AS tag_ids
FROM cars c
INNER JOIN car_tags ct ON ct.car_id = c.id
WHERE c.id IN (
    SELECT t1.id
    FROM cars t1
    INNER JOIN car_tags t2 ON t2.car_id = t1.id
    WHERE t2.tag_id IN (3, 4)
    GROUP BY t1.id
    HAVING COUNT(DISTINCT t2.tag_id) = 2
)
GROUP BY c.id
ORDER BY c.id;

    The subquery finds all cars having both tags of id 3 and 4. This subquery is used to restrict the outer query to only cars having these two tags.

    Login or Signup to reply.
  2. 0

    How about using exist? You may need to dynamically construct the where part though:

    SELECT c.id
FROM cars c
WHERE EXISTS(SELECT 1 FROM car_tags ct WHERE ct.car_id = c.id AND ct.tag_id=3)
  AND EXISTS(SELECT 1 FROM car_tags ct WHERE ct.car_id = c.id AND ct.tag_id=4)
-- more exist if the list is longer
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search