MySQL select statement with CASE or IF ELSEIF Not sure how to get the result

Question

I have a two tables   One has manufacturer information and includes the regions where they can sell   The other has their products for sale   We have to limit visibility of the product based on the regions   This is like Netflix have videos in their system that can only be viewed Everywhere  1   only in Canada  2   only in USA  3    I am trying to make a query that tells me where the product can be viewed based on the settings in the manufacturer table   For example  in the manufacturer table  there are two fields called expose new and expose used each of which will have a value of  1 2 or 3 to limit where their new or used videos can seen   When the videos are added  they are not assigned an  expose  value and this is meant to be done on the fly when adding them to our index depending on the current manufacturer s expose new or expose used values   What I am trying to get is the item details and the computed value for where it can be seen based on whether it is new or used and the rule value assigned to the manufacturer for all their new or used products   I need this single digit on a per-product basis to conditionally display it in a list   The following does not work  but you will get the idea of what I am trying to do   I have tried this with CASE statements and the following WRONG IF ELSEIF statement   Any help to debugger this and point me in the right direction would be appreciated   SELECT  t2 company name  t2 expose new      1 2 or 3 t2 expose used     1 2 or 3 t1 title  t1 seller  t1 status     can be new or used  SELECT  IF status   New      select expose new from manufacturers where id   t1 seller  1   ELSEIF t1 status   Used      select expose used from manufacturers where id   t1 seller  1   END IF   as  expose  FROM  products  t1 join manufacturers t2 on t2 id   t1 seller where t1 seller   4238   Here is a CASE version that actually seems to execute but always results in the first value no matter what happens to be true  in this case 1    I am not sure that I can have the addition of another test with the AND in each WHEN statement but it does not give an error  only the wrong result   SELECT  t2 company name  t2 expose new  t2 expose used  t1 title  t1 status   CASE status    when  New  and t2 expose new   1 then 1    when  New  and t2 expose new   2 then 2    when  New  and t2 expose new   3 then 3    when  Used  and t2 expose used   1 then 1    when  Used  and t2 expose used   2 then 2    when  Used  and t2 expose used   3 then 3 END as expose FROM  products  t1 join manufacturers t2 on t2 id   t1 seller where t1 seller   4238

User · Accepted Answer

Try this query -  SELECT    t2 company name    t2 expose new    t2 expose used    t1 title    t1 seller    t1 status    CASE status       WHEN  New  THEN t2 expose new       WHEN  Used  THEN t2 expose used       ELSE NULL   END as  expose  FROM    products  t1 JOIN manufacturers t2   ON     t2 id   t1 seller WHERE   t1 seller   4238

User · Answer

Another way of doing this is using nested IF statements  Suppose you have companies table and you want to count number of records in it  A sample query would be something like this  SELECT IF        count     gt  15         good         IF            count     gt  10             average              poor                     as data count        FROM companies   Here second IF condition works when the first IF condition fails  So Sample Syntax of the IF statement would be IF   CONDITION  THEN  ELSE   Hope it helps someone

User · Answer

Syntax    CASE value WHEN  compare value  THEN result   WHEN  compare value  THEN result        ELSE result   END   Alternative   CASE WHEN  condition  THEN result   WHEN  condition   THEN result        mysql gt  SELECT CASE  WHEN 2 gt 3 THEN  this is true  ELSE  this is false  END    -------------------------------------------------------------    CASE  WHEN 2 gt 3 THEN  this is true  ELSE  this is false  END    -------------------------------------------------------------    this is false                                                   -------------------------------------------------------------    I am use   SELECT  act        CASE          WHEN  lises session date IS NOT NULL AND ses session date IS NULL  THEN lises location id         WHEN  lises session date IS NULL AND ses session date IS NOT NULL  THEN ses location id         WHEN  lises session date IS NOT NULL AND ses session date IS NOT NULL AND lises session date gt ses session date  THEN ses location id         WHEN  lises session date IS NOT NULL AND ses session date IS NOT NULL AND lises session date lt ses session date  THEN lises location id     END AS location id FROM activity AS act LEFT JOIN li sessions AS lises ON lises activity id   act id AND  lises session date  gt   now   LEFT JOIN session AS ses ON  ses activity id   act id AND  ses session date  gt   now   WHERE act id

[mysql] MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result

Examples related to mysql

Examples related to select

Examples related to case