Select query with row constructors
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.