Search code examples
mysqlmany-to-manyentity-attribute-value

Mysql Many-to-many (AND Where) select

i have this database setup:

  • products

    • id
    • [fields...]

  • tags

    • id
    • [fields...]

  • product_tag

    • id
    • product_id
    • tag_id

If have this records in my database

  • Products
    • Product A [id: 1]
    • Product B [id: 2]
    • Product C [id: 3]
  • Tags
    • Tag A [id: 1]
    • Tag B [id: 2]
    • Tag C [id: 3]
  • Produc_Tag
    • [product_id: 1, tag_id: 1]
    • [product_id: 1, tag_id: 2]
    • [product_id: 2, tag_id: 1]
    • [product_id: 2, tag_id: 3]
    • [product_id: 3, tag_id: 1]
    • [product_id: 3, tag_id: 2]

How do i query to get the products that have tag_id 1 AND 2 (it must by products with tag_id 1 AND 2) in this example: "Product A" and "Product C"

Solution

  • Use this

    SELECT P.id
FROM products P
INNER JOIN products_tags PT ON PT.product_id = P.id
WHERE PT.tag_id IN (1,2)
GROUP BY P.id
HAVING COUNT(PT.*) = 2