CODEWITHSUNDEEP
mysql
Andreas
Andreas

Reputation: 7550

How to find all products with some properties?

I'm listing product properties in a MySQL table where each row contains a product ID prod and a property ID prop. If a product has three properties, this results in three rows for that product. Example table:

prod | prop
-----+-----
1    | 1
2    | 1
2    | 2
2    | 3
3    | 2
3    | 4

How can I find which products have both properties #1 and #2 (product #2)?

The only way that I can think of is one select and inner join per property, but I think that would be very inefficient. It's a search function for a website and has to work for 10k lines in the table and 10 requested properties.

Upvotes: 0

Views: 66

Answers (1)

zerkms
zerkms

Reputation: 254926

  SELECT prod
    FROM tbl
   WHERE prop IN (1, 2)
GROUP BY prod
  HAVING COUNT(*) = 2

And if there will be always 2 properties to find - then INNER JOIN would be a bit more efficient:

    SELECT t1.p
      FROM tbl t1
INNER JOIN tbl.t2 ON t2.prod = t1.prod
                 AND t2.prop = 2
     WHERE t1.prop = 1

The recommended index for this query to be efficient as much as possible is a compound one (prop, prod)

Upvotes: 2

Related Questions