Using IF condition in mysql query

Question

Hi I am trying to join the table depending on the value of another table. In my scenario, I want to join table job when message.type is job else join with table post_ads. I had written below MySql query, 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

Please provide the error message. Also is there PHP to the question, sounds like just mysql.. — chris85
– chris85, Commented Aug 22, 2017 at 2:50

Naga · Accepted Answer · 2017-08-22 04:21:56Z

3

First of all, you have misused case statement. It should be inside select statement not in left join. 2nd, you cannot select multiple fields when we use case statement. - To over come this issue you can use CONCAT function with user defined contastant and manipulate in the server side script.

You should write query something like the below one,

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

edited Aug 22, 2017 at 4:21

answered Aug 22, 2017 at 3:09

Naga

2,1683 gold badges18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user254153 Over a year ago

thank you for the time and is it possible to use job.* instead if I cannot select multiple fields?

Naga Over a year ago

No, You cannot use * inside case statement. You can use CONCAT or equivalent function since it will return a single filed value.

user254153 Over a year ago

Ok. Thank you for your time.

matisa · Accepted Answer · 2017-08-22 04:36:22Z

1

Try something like this:

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

modify it to fit your query.

EDIT

went again trough your code and I think that you miss brackets:

 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

edited Aug 22, 2017 at 4:36

answered Aug 22, 2017 at 2:53

matisa

5291 gold badge4 silver badges28 bronze badges

1 Comment

chris85 Over a year ago

Please format your answer. meta.stackexchange.com/questions/22186/…

Collectives™ on Stack Overflow

Using IF condition in mysql query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related