CODEWITHSUNDEEP
sqlpostgresql
Nick Vanderbilt
Nick Vanderbilt

Reputation: 2535

How to find all the products with specific multi attribute values

I am using postgresql.

I have a table called custom_field_answers. The data looks like this

Id | product_id | value      | number_value | 
4  | 2          |            | 117          |
3  | 1          |            | 107          |
2  | 1          | bangle     |              |
1  | 2          | necklace   |              |

I want to find all the products which has text_value as 'bangle' and number_value less than 50.

Here was my first attempt.

SELECT "products".* FROM "products" INNER JOIN "custom_field_answers" 
ON "custom_field_answers"."product_id" = "products"."id" 
WHERE ("custom_field_answers"."value" ILIKE 'bangle')

Here is my second attempt.

 SELECT "products".* FROM "products" INNER JOIN "custom_field_answers" 
ON "custom_field_answers"."product_id" = "products"."id" 
where ("custom_field_answers"."number_value" < 50)

Here is my final attempt.

SELECT "products".* FROM "products" INNER JOIN "custom_field_answers" 
ON "custom_field_answers"."product_id" = "products"."id" 
WHERE ("custom_field_answers"."value" ILIKE 'bangle') 
AND ("custom_field_answers"."number_value" < 50)

but this does not select any product record.

Upvotes: 1

Views: 259

Answers (5)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52127

Didn't actually test it, but this general idea should work:

SELECT *
FROM products
WHERE
    EXISTS (
        SELECT *
        FROM custom_field_answers
        WHERE
            custom_field_answers.product_id = products.id
            AND value = 'bangle'
    )
    AND EXISTS (
        SELECT *
        FROM custom_field_answers
        WHERE
            custom_field_answers.product_id = products.id
            AND number_value < 5
    )

In plain English: Get all products such that...

  • there is a related row in custom_field_answers where value = 'bangle'
  • and there is (possibly different) related row in custom_field_answers where number_value < 5.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562573

A WHERE clause can only look at columns from one row at a time.

So if you need a condition that applies to two different rows from a table, you need to join to that table twice, so you can get columns from both rows.

SELECT p.*
FROM "products" AS p
INNER JOIN "custom_field_answers" AS a1 ON p."id" = a1."product_id"
INNER JOIN "custom_field_answers" AS a2 ON p."id" = a1."product_id" 
WHERE a1."value" = 'bangle' AND a2."number_value" < 50

Upvotes: 1

Jake Feasel
Jake Feasel

Reputation: 16955

Your bangle-related number_value fields are null, so you won't be able to do a straight comparison in those cases. Instead, convert your nulls to 0s first.

SELECT "products".* FROM "products" INNER JOIN "custom_field_answers" 
ON "custom_field_answers"."product_id" = "products"."id" 
WHERE ("custom_field_answers"."value" LIKE '%bangle%') 
AND (coalesce("custom_field_answers"."number_value", 0) < 50)

Upvotes: 0

drdwilcox
drdwilcox

Reputation: 3951

It produces no records because there is no custom_field_answers record that meets both criteria. What you want is a list of product_ids that have the necessary records in the table. Just in case no one gets to writing the SQL for you, and until I have a chance to work it out myself, I thought I would at least explain to you why your query is not working.

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100195

This should work:

SELECT p.* FROM products LEFT JOIN custom_field_answers c ON (c.product_id = p.id AND c.value LIKE '%bangle%' AND c.number_value

Hope it helps

Upvotes: 0

Related Questions