MySQL查询中的WHERE和AND

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.

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.

these will not work after your update

try this SELECT DISTINCT prodID FROM table WHERE propID=2 OR propID=5 another way to do it is like this SELECT DISTINCT prodID FROM table WHERE propID IN(2,5)

EDIT:

try this instead

SELECT prodID FROM table WHERE propID=2 OR propID=5 GROUP BY prodID HAVING COUNT(DISTINCT prodID) = 2

NOTE: if you want to use this query accept AlmaDo's answer as it was proposed before mine.

you could do it with a subquery

SELECT prodID FROM (
SELECT prodID, COUNT(DISTINCT prodID) as filter_col FROM myTable
WHERE propID IN(2,5)
GROUP BY prodID
  )t 
WHERE filter_col = 2;

DEMO

You want to be using OR instead of AND. AND looks for a record where both conditions return true, which in this case is none. OR will return records where either of them are true.

Since you want both conditions to be true OR does not work.

A solution is to join the table with itslef and then add a where condition.

SELECT t1.prodID
FROM table t1
INNER JOIN table AS t2 ON t1.prodID = t2.prodID AND t2.propID = 2
WHERE t1.propID = 5

But there might be better solutions.

This will work too: Using a subquery to filter products with one property, and then filter the ones with also the other property. fiddle

SELECT DISTINCT T.PRODID 
FROM T INNER JOIN (SELECT PRODID FROM T WHERE PROPID=2) AS TABLE2 
ON T.PRODID = TABLE2.PRODiD
WHERE T.PROPID = 5

Try this

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

Either with count and having, or:

select * from products 
where (
  select count(distinct propID) from pivot_table where products.ID = pivot_table.prodID 
    and pivot_table.propID IN (2,5)
) = 2

IN(..) part and = 2 should be dynamic, according to your needs.

distinct will solve duplicates issue, however @Alma Do has better solution, that requires simple query without dependant subqueries.