MySQL left join where 2nd object is AND is

Question

Database

products

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(128) | NO   | UNI | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

tags

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(128) | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

products_tags

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| product_id | int(11) | NO   | MUL | NULL    |                |
| tag_id     | int(11) | NO   | MUL | NULL    |                |
+------------+---------+------+-----+---------+----------------+

Goal

Return products that are tagged by 2+ tags, for example "present" and "birthday".

It might look something like:

SELECT p.name FROM products p
LEFT JOIN products_tags p_t ON p_t.product_id = p.id
LEFT JOIN tags t ON t.id = p_t.tag_id
WHERE <what-is-the-condition?>

Missing the correct condition that would select by tags which are both present and birthday.

Something like:

WHERE t.name = 'present' AND t.name = 'birthday';

Answer 1

The table tags is not needed.
Join products to products_tags, group by product and set the condition in the HAVING clause:

SELECT p.id, p.name 
FROM products p INNER JOIN products_tags p_t 
ON p_t.product_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(*) >= 2