SELECT from Multiple IF conditions in Where Clause-CodePudding

I have a following query

SELECT * FROM products a, productImgs b  WHERE a.visible=1 AND a.Type IN('Accessories','Clothing','Electronics') ORDER BY a.visibleOrder ASC LIMIT 100

In the above query I need to add IF condition that IF a.Type is Accessories then I need certain a.Brands to select from and if a.Type is Clothing then I need to select from certain a.Brands

For a.Type 'Accessories' ->  a.Brands IN (ALL)
For a.Type 'Clothing' ->  a.Brands IN ('A','B','C')
For a.Type 'Electronics' ->  a.Brands IN ('D','E','F')

CodePudding user response：

Use a CASE expression, but you should also write a proper join with an ON clause for the 2 tables:

SELECT * 
FROM products a INNER JOIN productImgs b
ON .....
WHERE a.visible=1 
  AND CASE a.Type 
        WHEN 'Accessories' THEN 1
        WHEN 'Clothing' THEN a.Brands IN ('A','B','C')
        WHEN 'Electronics' THEN a.Brands IN ('D','E','F')
      END 
ORDER BY a.visibleOrder ASC LIMIT 100;

CodePudding user response：

Use parentheses and and/or conditions like you would with any other where clause:

where a.visible = 1 and (
  a.Type = 'Accessories' or
  a.Type = 'Clothing'    and a.Brands IN ('A', 'B', 'C') or
  a.Type = 'Electronics' and a.Brands IN ('D', 'E', 'F')
)

This should give MySQL chance to use indexes.