SQL Query to compare multiple data in one column

Question

I have these tables

product

product_id      name
51                a
52                 b
53                 c
54                 s
55                 e

product_filter

product_id      filter_id
52                 11
51                 22
51                 33
52                 33
54                 11

filter_group_description

filter_group_id     filter_id        name
1                     11             white  
1                     22             black
2                     33             formals
2                     44             casuals

filter_group

filter_group_id      name
1                   Colour
2                   Style

What I want here is product name where colour is black or white but style should be formals.

****UPDATE*****

I want to integrate the query in this query:



           SELECT DISTINCT oc_product.product_id, oc_product.image, oc_product.name, oc_product_option.option_id, oc_product.price,

            ifnull((SELECT oc_product_special.price
            FROM oc_product_special
            WHERE oc_product_special.product_id = oc_product.product_id
            AND '$date1' between oc_product_special.date_start and oc_product_special.date_end),0) as special_price, 

            ifnull((SELECT DISTINCT avg( oc_review.rating )
            FROM oc_review
            WHERE oc_review.product_id = oc_product.product_id
            GROUP BY oc_product.product_id),0) as rating, 

            ifnull((SELECT DISTINCT count(      
                oc_review.product_id)
            FROM oc_review
            WHERE oc_review.product_id = oc_product.product_id 
            GROUP BY oc_product.product_id),0) as rated 
            FROM oc_product, oc_product_to_category, oc_product_option, oc_review $from 

            where  oc_product_option.product_id = oc_product.product_id
            AND  oc_product_to_category.category_id = $category_id
            AND oc_product_to_category.product_id = oc_product.product_id

By your sample data given, you have no records that meet your requirements. b is black and white, but isn't formal. The only formal one is s, but it's only white. Can you please show your expected results? — Siyual
– Siyual, Commented Jun 16, 2016 at 12:47
@Siyual how about now, looking at the data if i want a product name where its black or white and it should be formals — Mohsin Shaikh
– Mohsin Shaikh, Commented Jun 16, 2016 at 14:03

Hart CO · Accepted Answer · 2016-06-17 12:17:22Z

1

You can use conditional aggregation in a HAVING clause for this:

SELECT p.name
FROM product p
JOIN product_filter pf
  ON p.product_id = pf.product_id
JOIN filter_group_description fgd
  ON pf.filter_id = fgd.filter_id
JOIN filter_group fg
  ON fgd.filter_group_id = fg.filter_group_id
GROUP BY p.name
HAVING  MAX(fgd.name = 'white')+MAX(fgd.name = 'black') > 0
    AND MAX(fgd.name = 'formals') = 1

MySQL returns 0 or 1 for true/false expressions like fgd.name = 'white', other databases you'd have to use a MAX(CASE WHEN fgd.name = 'white' THEN 1 END) instead.

Demo: SQL Fiddle

Edit: If you simply want to pass a list of colors and a list of styles, you could use a CASE expression with IN in the HAVING clause which would make it easy to make the query dynamic:

SELECT p.name
FROM product p
JOIN product_filter pf
  ON p.product_id = pf.product_id
JOIN filter_group_description fgd
  ON pf.filter_id = fgd.filter_id
JOIN filter_group fg
  ON fgd.filter_group_id = fg.filter_group_id
GROUP BY p.name
HAVING  MAX(CASE WHEN fgd.name IN ('black','white') THEN 1 ELSE 0 END) > 0
    AND MAX(CASE WHEN fgd.name IN ('formals') THEN 1 ELSE 0 END) > 0

Dynamically handling new filter groups would be a little more complex.

edited Jun 17, 2016 at 12:17

answered Jun 16, 2016 at 15:28

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mohsin Shaikh Over a year ago

can u modify this query with IN operator coz the query is going to be dynamic, and also if possible by filter_id

Hart CO Over a year ago

So you'd have multiple variables, color and style and each one could have multiple options checked off?

Hart CO Over a year ago

I updated to using IN but making it dynamically handle new filter groups will be a little more complex.

Mohsin Shaikh Over a year ago

can you please integrate your query with mine, i am having a tough time integrating them both. i have update the question with my complete query

Phritzy · Accepted Answer · 2016-06-16 15:04:47Z

0

SELECT p.name
FROM product p
JOIN
    ( SELECT pf.*,
             fgd.filter_group_id,
             fgd.name 'fgd_name',
             fg.name 'fg_name'
      FROM product_filter pf
      JOIN filter_group_description fgd
      ON pf.filter_id = fgd.filter_id
      JOIN filter_group fg
      ON fgd.filter_group_id = fg.filter_group_id
      WHERE fg.name = 'Colour'
    ) filter1
ON p.product_id = filter1.product_id
JOIN
    ( SELECT pf.*,
             fgd.filter_group_id,
             fgd.name 'fgd_name',
             fg.name 'fg_name'
      FROM product_filter pf
      JOIN filter_group_description fgd
      ON pf.filter_id = fgd.filter_id
      JOIN filter_group fg
      ON fgd.filter_group_id = fg.filter_group_id
      WHERE fg.name = 'Style'
    ) filter2
ON p.product_id = filter2.product_id
WHERE filter1.fgd_name IN
( 'black', 'white' )
      AND filter2.fgd_name = 'formals'

Heres a SQLFiddle: http://sqlfiddle.com/#!9/8ab6e3/1

answered Jun 16, 2016 at 15:04

Phritzy

1975 silver badges12 bronze badges

1 Comment

Mohsin Shaikh Over a year ago

what if i want to make the query dynamic i.e if a new filter group is added and thus same group has more filters, the same query cannot be used. can you provide me with a query to fulfill such requirement,

Collectives™ on Stack Overflow

SQL Query to compare multiple data in one column

product

product_filter

filter_group_description

filter_group

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

product

product_filter

filter_group_description

filter_group

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related