CODEWITHSUNDEEP
sqlpostgresql
Ivan M
Ivan M

Reputation: 330

SELECT one entry with two left joins. SQL

Example if two products

id  name
1   product A
2   product B

And for each products I've attributes

id product_id value
1  1           1
2  1           2
3  2           3
3  2           4

And I need to select products by value of attributes. I need products which have attributes with 1 AND 2 values.

This query doesn't work:

SELECT *
FROM product
LEFT JOIN attribute ON product.id = attribute.product_id
WHERE attribute.value = 1 AND attribute.value = 2;

Upvotes: 1

Views: 117

Answers (4)

jarlh
jarlh

Reputation: 44766

Do a group by to find product id's with both 1 and 2 attributes. Select from products where product id found by that group by:

SELECT *
FROM product_table
WHERE id IN (select product_id
             from attribute_table
             where value in (1,2)
             group by product_id
             having count(distinct value) = 2)

Alternative solution, double join:

SELECT *
FROM product_table
  JOIN attribute_table a1 ON product_table.id = a1.product_id
                          AND a1.value = 1
  JOIN attribute_table a2 ON product_table.id = a2.product_id
                          AND a2.value = 2

Upvotes: 3

pozs
pozs

Reputation: 36244

To rephrase your question, you really need those products, which has both 1 and 2 within the values of their attributes:

SELECT    product.*
     -- , array_agg(attribute.value) attribute_values
     -- uncomment the line above, if needed
FROM      product
LEFT JOIN attribute ON product.id = attribute.product_id
GROUP BY  product.id
HAVING    array_agg(attribute.value) @> ARRAY[1, 2];

Upvotes: 3

Tushar
Tushar

Reputation: 3623

SELECT *
FROM product p
LEFT JOIN attribute a ON p.id = a.product_id
WHERE a.value IN ('1','2')

Upvotes: 3

Matt
Matt

Reputation: 15071

If you mean values 1 OR 2

SELECT *
FROM product p
LEFT JOIN attribute a ON p.id = a.product_id
WHERE a.value IN ('1', '2');

Upvotes: 1

Related Questions