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

Question

I have two tables. One has information about manufacturers and includes regions where they can sell. Another has his own products for sale. We must limit product visibility based on regions. This is similar to the fact that Netflix has videos in its system that can be viewed only everywhere (1), only in Canada (2), only in the USA (3).

I am trying to make a request that tells me where the product can be viewed based on the settings in the manufacturer’s table.

For example, in the manufacturer’s table, there are two fields named 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 be seen.

When videos are added, they are not assigned the value "set", and this should be done on the fly, adding them to our index, depending on the current producer, expose_new or expose_used.

What I'm trying to get is the product details and the calculated 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 of their new or used products. I need this single digit for each product to conditionally display it in the list.

The following does not work, but you get an idea of ​​what I'm trying to do. I tried this with the CASE statements and the following WRONG IF / ELSEIF statement.

Any help for debugging this and pointing 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 version of CASE that apparently executes, but always leads to the first value, regardless of what is true (in this case 1). I am not sure that I can add another test with AND to each WHEN statement, but it does not give an error, only an incorrect 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 

Answer 1

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 

Answer 2

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 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