CODEWITHSUNDEEP
sqlpostgresqljoin
Michael Victor
Michael Victor

Reputation: 891

WHERE Clause for One-To-Many Association

I have two tables Products and ProductProperties. 

Products
  name - string
  description - text
  etc etc

ProductProperties
  product_id - integer
  property_id - integer

There is also a table Properties which basically stores the list of property names and their attributes

How can I implement a SQL command that finds a product with the property_ids (A or B or C) AND (X or Y or Z)

I've got upto here: 

SELECT  DISTINCT "products".* 
FROM "products" 
INNER JOIN "product_properties" ON "product_properties"."product_id" = "products"."id" AND "product_properties"."deleted_at" IS NULL 
WHERE "products"."deleted_at" IS NULL 
AND (product_properties.property_id IN ('504, 506, 403')) 
AND (product_properties.property_id IN ('520, 501, 502'))

But it doesn't really work since it's looking for a Product Property which has both values 504 and 520, which will never exist.

Would appreciate some help!

Upvotes: 0

Views: 59

Answers (3)

SADIK Younsse
SADIK Younsse

Reputation: 166

Hi try this queries i just thinking about it so i didn't try any of them check i got the idea i want to do

SELECT  DISTINCT "products".* 
FROM products pr
WHERE  id IN
(
    SELECT product_id FROM ProductProperties WHERE property_id IN (504,520)
    GROUP BY product_id
    HAVING Count(*) = 2
) AND "products"."deleted_at" IS NULL 

SELECT  DISTINCT "products".* 
FROM products pr,  INNER JOIN (
    SELECT product_id,count(*) as nbr FROM ProductProperties WHERE property_id IN (504,520)
    GROUP BY product_id
) as temp ON temp.product_id = pr.id
WHERE "products"."deleted_at" IS NULL  AND temp.nbr = 2

and also you can check this one as well ( you can use also the join in where clause instead of using INNER JOIN)

SELECT  DISTINCT products.* FROM products as p 
INNER JOIN product_properties as p1 ON p1.product_id = p.id 
INNER JOIN product_properties as p2 ON p2.product_id = p.id 
WHERE p.deleted_at IS NULL 
AND p1.property_id = '504' AND p1.deleted_at IS NULL
AND p2.property_id = '520' AND p2.deleted_at IS NULL

Upvotes: 0

Sebas
Sebas

Reputation: 21542

You need to define intermediate resultsets on a property group basis:

SELECT DISTINCT p.* 
FROM products p
JOIN product_properties groupA ON groupA.product_id = p.id AND groupA.deleted_at IS NULL AND groupA.property_id IN ('504') 
JOIN product_properties groupB ON groupB.product_id = p.id AND groupB.deleted_at IS NULL AND groupB.property_id IN ('520')
WHERE p.deleted_at IS NULL

You see, you detected the problem yourself very nicely: "But it doesn't really work since it's looking for a Product Property which has both values 504 and 520, which will never exist."

Indeed, recordsets are immutable within a query, all single criteria applied to them are applied all at once. You need to duplicate each table and apply individual criteria to them.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

One method uses exists or in:

select p.*
from products p
where p.id in (select pp.product_id
               from product_properties pp
               where pp.propertyid in ('504', '520')
              );

This saves you from having to use distinct in the outer query.

If, perchance, you really mean finding the products that have all the properties, then a join and group by work:

select p.*
from products p join
     product_properties pp
     on p.id = pp.product_id
where pp.propertyid in ('504', '520')
group by p.id -- yes, this is allowed in Postgres
having count(*) = 2;

Upvotes: 0

Related Questions