Mysql CASE WHEN JOIN语句错误 [英] Mysql CASE WHEN JOIN Statement Error

问题描述

Mysql查询:

SELECT *
FROM `pet_info` LEFT JOIN
     `lostpets`
     ON `pet_info`.`id` = `lostpets`.`petid` LEFT JOIN
     `pet_images`
     ON `pet_info`.`id` = `pet_images`.`petid` 
     CASE WHEN `pet_info`.`pet_cat` = 2
          THEN LEFT JOIN `cat_breeds`
               ON `cat_breeds`.`id` = `pet_info`.`pet_breed`
         WHEN `pet_info`.`pet_cat` = 1
         THEN LEFT JOIN `dog_breeds`
              ON `dog_breeds`.`id` = `pet_info`.`pet_breed`
WHERE `pet_info`.`pet_user_id` = 581

错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN `pet_info`.`pet_cat` = 2 THEN LEFT JOIN `cat_breeds` ON `cat_breeds`.`' at line 1 

我该如何解决错误?哪里有错误?请帮助我.

How can I solve error? Where is a error? Please help me.

推荐答案

我认为您打算这样做:

SELECT *
FROM `pet_info` LEFT JOIN
     `lostpets`
     ON `pet_info`.`id` = `lostpets`.`petid` LEFT JOIN
     `pet_images`
     ON `pet_info`.`id` = `pet_images`.`petid` LEFT JOIN
     `cat_breeds`
     ON `cat_breeds`.`id` = `pet_info`.`pet_breed` AND
        `pet_info`.`pet_cat` = 2 LEFT JOIN
     `dog_breeds`
     ON `dog_breeds`.`id` = `pet_info`.`pet_breed` AND
        `pet_info`.`pet_cat` = 1
WHERE `pet_info`.`pet_user_id` = 581;

注意:

• 使用这样的查询，您不应使用SELECT *，而应显式选择所需的列.不同的表具有相同名称的列.
• 您应该使用列别名.我没有将它们放入查询中，但这是一个好主意.
• 在真实查询中，您将在SELECT中具有表达式，以合并cat_breeds和dog_breeds中的列，例如COALESCE(cat_breeds.col1, dog_breeds.col1) as col1.

• With a query like this, you should not use SELECT *, you should explicitly choose the columns you want. The different tables have columns with the same name.
• You should use column aliases. I didn't put these into the query, but they are a good idea.
• In a real query, you would have expressions in the SELECT to combine columns from cat_breeds and dog_breeds, such as COALESCE(cat_breeds.col1, dog_breeds.col1) as col1.

这篇关于Mysql CASE WHEN JOIN语句错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！