Reputation: 513
I have this tables:
products -> hasMany -> categories
Table: products
+----+--------+
| id | title |
+----+--------+
| 1 | Prod 1 |
| 2 | Prod 2 |
| 3 | Prod 3 |
+----+--------+
Table: categories
+----+-------+------------+
| id | title | product_id |
+----+-------+------------+
| 1 | Cat 1 | 1 |
| 2 | Cat 2 | 1 |
| 3 | Cat 3 | 2 |
| 4 | Cat 1 | 1 |
+----+-------+------------+
How can i query the products which are in both categories "Cat 1" AND "Cat 2" in my example i want only find "Prod 1"
Upvotes: 0
Views: 65
Reputation: 191
My intuition is that you should change the database, so you instead have a habtm relation.
So your tables should looks like this:
Products (id, title)
ProductCategories (product_id, category_id)
Categories (id, title)
So the product can belong to many categories.
Then you can look up your table for products belonging to category with ID = 1 and category with ID = 3, by doing following query:
MySQL query
SELECT Products.id, Products.title
FROM Products
WHERE
Products.id IN
(
SELECT c1.product_id
FROM ProductCategories AS c1
INNER JOIN ProductCategories AS c2
ON c1.product_id = c2.product_id
WHERE c1.category_id = 1 AND c2.category_id = 3
);
Upvotes: 1