I am using RDBMS MySQL 5.7. I have two tables with the following structures:
Table A - primary table - products:
+----+-----------+-------------+------------+--------+
| id | title | description | createdAt | status |
+----+-----------+-------------+------------+--------+
| 1 | Product A | Desc A | 2019-07-19 | 1 |
| 2 | Product B | Desc B | 2019-07-04 | 1 |
+----+-----------+-------------+------------+--------+
Table B - product_categories -> one-to-many model
+----+-----------+------------+
| id | productId | categoryId |
+----+-----------+------------+
| 1 | 1 | 3 |
| 2 | 1 | 5 |
| 3 | 2 | 5 |
| 4 | 2 | 7 |
| 5 | 2 | 9 |
+----+-----------+------------+
productId and categoryId are foreign keys.
I want to select rows from products table which have categories 3 and 5 both. The query should return the only row with id 1.
I have such a query:
SELECT p.id
FROM products p
inner join product_categories pc on p.id = pc.productId
WHERE (pc.productId, pc.categoryId) IN ((p.id, 3), (p.id, 5))
GROUP BY productId
HAVING COUNT(productId) = 2
How you would simplify it? Without using row constructors.