CODEWITHSUNDEEP
phpmysql
user254153
user254153

Reputation: 1883

Using IF condition in mysql query

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

Upvotes: 4

Views: 64

Answers (2)

matisa
matisa

Reputation: 529

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

Upvotes: 1

Naga
Naga

Reputation: 2168

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

Upvotes: 3

Related Questions