MySQL查询中的WHERE和AND

Question

Hi I have a little problem and I can't find the solution. I'm pretty new in MySql.

Let's say I have this Table:

 _______________
|prodID| |propID|
|________|______|
|   1    | 2    |
|   1    | 5    |
|   2    | 6    |
|   2    | 7    |
|   3    | 5    |
|   3    | 2    |
|   3    | 9    |
|   4    | 5    |
|   4    | 3    |
|   5    | 2    |
|   5    | 5    |
|________|______|

prodID is the Product-ID and propID is the Property-ID.
Now I want to get the Products which for example have the property with propID=2 and the property with propID=5. Where the property in propID=2 is "made from glass" and propID=5 is "safe for children". I want that the products (prodID) I get are both. "Made from glass and safe for children". So the propIDs I would get are 1,3 and 5.

This hasn't worked for me:

SELECT prodID FROM table WHERE propID=2 AND propID=5

I hope it's clear what I mean and hope someone can help me.

Thanks

Edit:

Table is now changed at prodID=4. If I would try

SELECT prodID FROM table WHERE propID=2 OR propID=5

I would get 1,3,4 and 5. But that is not what I wanted.

Answer 1

The query you need is:

SELECT
  prodID
FROM
  t
WHERE
  propID IN (2,5)
GROUP BY
  prodID
HAVING
  COUNT(DISTINCT propID)=2

Also, note, that it's "exactly 2 properties". If you need "at least '2' and '5' in properties" in the result, then change COUNT(DISTINCT propID)=2 to COUNT(DISTINCT propID)>=2. Check this fiddle.