Using IF condition in mysql query

Question

Hey. I am trying to join a table depending on the value of another table. In my scenario, I want to join the table jobwhen message.typeis an else join activity with the table post_ads. I wrote a MySql query below, but its throwing syntax error. Please help.

 SELECT message.*,register.name, rg.name as to_user_name, post_ads.*
 FROM message 
 INNER JOIN register ON message.from_user_id = register.id 
 INNER JOIN register rg ON message.to_user_id = rg.id LEFT JOIN 

 CASE message.type WHEN 'job' THEN 

 SELECT 'job.title' as titles FROM job 
 WHERE (message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
 (message.`from_user_id` = '218' AND message.`to_user_id` = '196')

  ELSE

  SELECT 'post_ads.brand_category, post_ads.model_category, post_ads.titles, 
  post_ads.images, post_ads.ads_main_pic'  FROM post_ads  
  WHERE (message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
  (message.`from_user_id` = '218' AND message.`to_user_id` = '196')

  END as post_ads

Answer 1

, case. select . -, , case. - , CONCAT script.

:

SELECT message.*,register.name, rg.name AS to_user_name, post_ads.*,

CASE `message`.`type` WHEN 'job' THEN 

(SELECT job.title  FROM job 
WHERE (message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
(message.`from_user_id` = '218' AND message.`to_user_id` = '196')) 

ELSE

(SELECT  post_ads.titles FROM post_ads  
WHERE (message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
(message.`from_user_id` = '218' AND message.`to_user_id` = '196'))

END AS post_ads

FROM message 
INNER JOIN register ON message.from_user_id = register.id 
INNER JOIN register rg ON message.to_user_id = rg.id 

Answer 2

- :

$query = ("select * from `table1` where (`param`='$param' AND `$param2 `='$param2') OR `param3`='$param3");

Hide result

, .

, , :

 SELECT message.*,register.name, rg.name as to_user_name, post_ads.*
 FROM message 
 INNER JOIN register ON message.from_user_id = register.id 
 INNER JOIN register rg ON message.to_user_id = rg.id LEFT JOIN 

 CASE message.type WHEN 'job' THEN 

 SELECT 'job.title' as titles FROM job 
 WHERE ((message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
 (message.`from_user_id` = '218' AND message.`to_user_id` = '196'))

  ELSE

  SELECT 'post_ads.brand_category, post_ads.model_category, post_ads.titles, 
  post_ads.images, post_ads.ads_main_pic'  FROM post_ads  
  WHERE( (message.`from_user_id` = '196' AND message.`to_user_id` = '218') OR 
  (message.`from_user_id` = '218' AND message.`to_user_id` = '196'))

  END as post_ads

Hide result