MySQL CASE LEFT JOINS 返回 NULL

Question

我有一个带有 CASE 语句的查询，该语句确定变量 object_name该变量源自 x_ambitions表或 x_trybes table 。下面的查询被组合起来，这样我就可以通过只执行一个 SQL 查询来保持简单。

我将 SELECT 语句分成两部分，以便您更好地理解。下面两个查询。工作并从数据库中提取正确的 object_name。

当我组合两个查询时，我遇到的问题是 case 'new_join_ambition','new_created_ambition','new_liked_ambition' object_name 在 LEFT JOIN 中返回 NULL .

在组合查询中，如果我带案例:'new_join_ambition','new_created_ambition','new_liked_ambition'上面'new_join_trybe','new_created_trybe','new_liked_trybe'案例。相反的情况会发生。 trybe 行返回 NULL 。

两个 SQL 查询:

A:(检索对象A)

SELECT 
    s.id, 
    s.object_id, 
    s.type, 
    s.postee_id, 
    s.user_id,
    s.text,
    s.registered,
    CONCAT(u.x_first_name,' ',u.x_last_name) AS postee_name,
    ui.image_id AS postee_image_id,
    CASE s.type
       WHEN 'new_join_ambition' 
            OR 'new_created_ambition' 
            OR 'new_liked_ambition'         
          THEN a.name
          ELSE 'a'
    END AS object_name
FROM 
    x_share s
LEFT JOIN 
    x_user u ON u.id = s.postee_id
LEFT JOIN 
    x_user_images ui ON ui.user_id = s.postee_id
LEFT JOIN 
    x_ambitions a ON s.type IN ('new_join_ambition', 'new_created_ambition', 'new_liked_ambition') AND s.object_id = a.id
LEFT JOIN 
    x_ambition_invites ai ON s.type IN ('new_join_ambition') AND s.object_id = ai.ambition_id AND s.postee_id = ai.to
LEFT JOIN 
    x_ambition_likes al ON s.type IN ('new_liked_ambition') AND s.object_id = al.ambition_id AND s.postee_id = al.profile_id
LEFT JOIN 
    x_ambition_owner aoo ON s.type IN ('new_created_ambition') AND s.object_id = aoo.ambition_id
WHERE 
    s.user_id = '%s'
ORDER BY 
    s.registered DESC

B:(检索对象B)

SELECT 
    s.id, 
    s.object_id, 
    s.type, 
    s.postee_id, 
    s.user_id,
    s.text,
    s.registered,
    CONCAT(u.x_first_name,' ',u.x_last_name) AS postee_name,
    ui.image_id AS postee_image_id,
    CASE s.type
       WHEN 'new_join_trybe'
            OR 'new_created_trybe' 
            OR 'new_liked_trybe'
          THEN t.name
          ELSE 'a'
    END AS object_name
FROM 
    x_share s
LEFT JOIN 
    x_user u ON u.id = s.postee_id
LEFT JOIN 
    x_user_images ui ON ui.user_id = s.postee_id
LEFT JOIN 
    x_trybes t ON s.type IN ('new_join_trybe', 'new_created_trybe', 'new_liked_trybe') AND s.object_id = t.id
LEFT JOIN 
    x_trybe_invites ti ON s.type IN ('new_join_trybe') AND s.object_id = ti.trybe_id AND s.postee_id = ti.to
LEFT JOIN 
    x_trybes_likes tl ON s.type IN ('new_liked_trybe') AND s.object_id = tl.trybe_id AND s.postee_id = tl.profile_id
LEFT JOIN 
    x_trybe_owner too ON s.type IN ('new_created_trybe') AND s.object_id = too.trybe_id
WHERE 
    s.user_id = '%s'
ORDER BY 
    s.registered DESC

我已经运行了这两个查询并捕获了这两个查询结果的图像。

A组:

B组:

如何将两者结合起来而不让 object_name 返回 NULL？如果您有任何疑问，请使用评论，我会毫不犹豫地回复。

提前致谢

Answer 1

我不知道你的查询的其余部分，但你的case陈述不正确。你有:

(CASE s.type
          WHEN 'new_join_ambition' OR 'new_created_ambition' OR 'new_liked_ambition'
          THEN a.name
          ELSE 'a'
END) AS object_name

OR最终将这些值视为数字，因此这相当于 WHEN 0 THEN . . . .

您想要的是这种形式的 case :

(CASE WHEN s.type IN ('new_join_ambition', 'new_created_ambition', 'new_liked_ambition')
      THEN a.name
      ELSE 'a'
 END) AS object_name