CODEWITHSUNDEEP
mysqlsql
Mohsin Shaikh
Mohsin Shaikh

Reputation: 33

SQL Query to compare multiple data in one column

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

Upvotes: 0

Views: 88

Answers (2)

Hart CO
Hart CO

Reputation: 34774

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.

Upvotes: 1

Phritzy
Phritzy

Reputation: 197

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

Upvotes: 0

Related Questions