CODEWITHSUNDEEP
sqlpostgresqljoin
Jo Erlang
Jo Erlang

Reputation: 327

Complicated SQL query

I have inherited a DB that has products, where each product has attributes, which are organised in groups ,

I need to construct a query that allows a user to specify a bunch of attributes and return the products that have at least one match in each group.

So if a product has a color of blue, size: xl and collarshape is vneck, and a user chooses blue, green, vneck xl xxl, then the above product would match. If the product only had the color yellow it would not match

Below is the table structure..

E.g. colors : blue, yellow, green, sizes: xl xxl l s, collarshape: vneck, crewneck

\ the products,

 CREATE TABLE products (
        id     integer,
        name    varchar(40),
        PRIMARY KEY(id)
    );

// join attributes and products 

    CREATE TABLE attributes_products (
        product_id     integer,
        attibute_id integer 
    );

// attributes // e.g blue, yellow, green , xl, xxl, l, s ,vneck, crewneck 

    CREATE TABLE attributes (
        id     integer,
        label varchar(40),
        attribute_group_id integer
    );

// e.g collarshape , color , size 

    CREATE TABLE attribute_group (
        id     integer,
        label varchar(40)
    );

Upvotes: 0

Views: 75

Answers (2)

Nandakumar V
Nandakumar V

Reputation: 4615

I think this query will give the desired output

SELECT * FROM products WHERE id IN (
    SELECT product_id 
    FROM attributes_products 
    GROUP BY product_id
    HAVING (array_agg(attibute_id) @>  ARRAY[90,93])
)


SELECT * FROM products 
INNER JOIN (
    SELECT product_id 
    FROM attributes_products 
    GROUP BY product_id
    HAVING (array_agg(attibute_id) @>  ARRAY[90,93])
) AS availableproduts ON products.id = availableproduts.product_id


SELECT * FROM products 
WHERE EXISTS (
    SELECT 1 
    FROM attributes_products 
    WHERE products.id = product_id
    GROUP BY product_id
    HAVING (array_agg(attibute_id) @>  ARRAY[90,93])
)

The 90,93 is the id of your attributes

The sqlfiddle is here

More details on the array function can be found in
Array Functions and Operators, Aggregate Functions

Upvotes: 0

Pinx0
Pinx0

Reputation: 1258

Something like this should do it.

SELECT p.id, p.name 
FROM products p
INNER JOIN attributes_products ap ON ap.product_id = p.id
INNER JOIN attributes a ON ap.attibute_id = a.id
WHERE a.id in (15,17,19,20) --list of attributes selected by user
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT a.attribute_group_id) = 3

Upvotes: 1

Related Questions