I'm trying to build a filter to quickly find the right product based on some specifications. But I can't get the MySQL to work. Been gooogling for a while now but can't find a similar question. I hope you can help me.
This is the products table
--------------------
| id | name |
--------------------
| 1 | Product 1 |
| 2 | Product 2 |
| 3 | Product 3 |
--------------------
This is the relation table for the specifications
--------------------------------
| id | specs_id | prod_id |
--------------------------------
| 1 | 1 | 1 |
| 2 | 5 | 1 |
| 3 | 6 | 2 |
| 4 | 9 | 3 |
| 5 | 11 | 2 |
---------------------------------
This is the MySQL how I want it to work.
$sql = "SELECT p.id, p.name
FROM products p
JOIN specs s ON p.id = s.prod_id
WHERE s.specs_id = 1
AND s.specs_id = 5
AND s.specs_id = 7
GROUP BY p.id";
This example will give no result
$sql = "SELECT p.id, p.name
FROM products p
JOIN specs s ON p.id = s.prod_id
WHERE s.specs_id = 1
AND s.specs_id = 5
GROUP BY p.id";
This will return product with ID 1
Item_id does not exist in your table. You also used AND insted of OR, thus no entry could match. None can have the specs_id 2,5, and 7 at the same time.
SELECT p.id, p.name
FROM products p
JOIN specs s
ON p.id = s.prod_id
WHERE s.specs_id = 5
OR s.specs_id = 2
OR s.specs_id = 7
GROUP BY p.id;
Maybe OR or IN is what you are looking for:
SELECT DISTINCT p.id, p.name FROM products p JOIN specs s ON p.id = s.prod_id WHERE s.specs_id IN (1,5);either
SELECT DISTINCT p.id, p.name FROM products p JOIN specs s ON p.id = s.prod_id WHERE s.specs_id=1 OR s.specs_id=5; Also, use DISTINCT instead of GROUP BY if you don't have aggregate functions.
You can do like first filter specs table with required specs_id and then make join that result with products table.
select p.id, p.name from
(select * from products p ) p
join (select * from specs where specs_id in (1,5,7)) s
on p.id = s.prod_id
group by p.id