Select query with row constructors

Question

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.

Answer 1

I want to select rows from products table which have categories 3 and 5 both.

SELECT productId
FROM product_categories
WHERE categoryId IN (3, 5)
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2 = COUNT(DISTINCT categoryId)

If (productId, categoryId) is set as unique by index, you may remove DISTINCT.

products table not needed at all (except the case when some value of productId exists in product_categories but not exists in products).

How can I do it with two tables: I have another table similar to product_categories, let's say product_highlights and it has one-to-many relationship as well. How can I query results with values (3, 5) in product_categories and (2, 4) in product_highlights?

SELECT t1.productId
FROM product_categories t1
JOIN product_highlights t2 ON t1.productId = t2.productId
WHERE t1.categoryId IN (3, 5)
  AND t2.highlightId IN (2, 4, 6) -- changed to 3 values
GROUP BY productId
-- JOIN products ON products.id = product_categories.productId
HAVING 2*3 = COUNT(DISTINCT categoryId, highlightId)

If (productId, parameterId) is set as unique by index in BOTH tables, you may remove DISTINCT.

Alternatively you can select productId for each table separately in subqueries, then join them in main query.