0

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.

  • Yes, numbers will be different but the number of values can change. Sometimes can be more, I will use it as a filter, the number of values can grow as the user selects options. @Akina – Shahin Jul 19 at 10:49
0

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.

  • Thank you for your reply. Let me clarify something. 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? – Shahin Jul 19 at 11:05
  • @Shahin Code added. – Akina Jul 19 at 11:19
  • Thank you @Akina. It is really helpful! – Shahin Jul 19 at 11:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.