CODEWITHSUNDEEP
mysqldatabasejoin
perqedelius
perqedelius

Reputation: 105

MySQL select from two table based on multiple conditions in same table

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

Upvotes: 1

Views: 1067

Answers (3)

slaakso
slaakso

Reputation: 9080

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);

or

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 do not have aggregate functions.

Upvotes: 1

Avinash Dalvi
Avinash Dalvi

Reputation: 9321

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

Upvotes: 0

Deadly Pointer
Deadly Pointer

Reputation: 322

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;

Upvotes: 2

Related Questions