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

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

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(*) > 15,
      'good',
      IF(
          count(*) > 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.

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> SELECT CASE  WHEN 2>3 THEN 'this is true' ELSE 'this is false' END; 
+-------------------------------------------------------------+
| CASE  WHEN 2>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>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<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 >= now()
LEFT JOIN session AS ses ON  ses.activity_id = act.id AND  ses.session_date >= now()
WHERE act.id