MYSQL select and join multiple where AND clauses from the same table

There is a table of products, and a table for product tags.
Trying to select all the products which have all the tags. So when a user refines their search, they only get the products with all the tags.

tag

id              PID       tag
------------------------------
0c212c04_Gold   0c212c04  Gold
0c212c04_500g   0c212c04  500g
0c212c04_Coin   0c212c04  Coin

Initially I tried an OR and IN, but that brought back all the results. Even if they only had 1 of the required tags

SELECT DISTINCT p.* 
FROM products p
INNER JOIN product_tags pt ON (p.PID = pt.PID)
WHERE pt.tag = 'Gold' OR pt.tag = '10oz' AND p.RRP BETWEEN 0 AND 1000000

SELECT DISTINCT p.* 
FROM products p
INNER JOIN product_tags pt ON (p.PID = pt.PID)
WHERE pt.tag IN ('Gold','10oz') AND p.RRP BETWEEN 0 AND 1000000

So I tried AND which brings back 0 results.

SELECT DISTINCT p.* 
FROM products p
INNER JOIN product_tags pt ON (p.PID = pt.PID)
WHERE pt.tag = 'Gold' AND pt.tag = '10oz' AND p.RRP BETWEEN 0 AND 1000000

How do I bring back all the products which has multiple tags of Gold and 10oz and Coin?

Answer

Here is an option that relies on condition aggregation and that can handle any number of conditions (just add more conditions to the HAVING clause):

SELECT p.id
FROM products p
INNER JOIN product_tags pt ON p.PID = pt.PID
WHERE p.rrp BETWEEN 0 AND 1000000
GROUP BY p.id
HAVING MAX(pt.tag = 'GOLD') = 1 AND MAX(pt.tag = '10oz') = 1

NB: if you need more columns from products, you would need to add them in the SELECT and in the GROUP BY clause (this will not affect the results of the query, since primary key product.id is already part of the non-aggregated columns).