php - 在 mysql 查询中使用 IF 条件

Question

您好，我正在尝试根据另一个表的值连接表。在我的场景中，当 message.type 是工作时，我想加入表 job，否则加入表 post_ads。我在下面写了 MySql 查询，但它抛出语法错误。请帮忙。

 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 函数和用户定义的常量并在服务器端脚本中进行操作。

你应该像下面这样写查询，

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