带有 INNER JOIN 的 MySQL CASE QUERY

Question

我有 1 个字符串类型的表。 (照片添加、照片评论等)然后我有 4 个其他表与这些类型相关联。

我需要做一个查询，根据类型从所有这些表中选择数据。

我创建了它，但它不起作用。

SELECT DISTINCT a.id, a.event, a.params, a.created_at, a.item_id, a.sender_id, a.receiver_id, u.name, u.id as userid 
FROM `eva49_lovefactory_activity` a, eva49_users u 
CASE a.event 
  WHEN "photo_add" THEN 
    INNER JOIN eva49_lovefactory_photos lp ON lp.id = a.item_id AND lp.fsk18 = -1
  WHEN "group_create" THEN 
     INNER JOIN eva49_lovefactory_groups lg ON lg.id = a.item_id AND lg.fsk18 = -1 
  WHEN "photo_comment" THEN 
     INNER JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND lic.fsk18 = -1 
  WHEN "profile_comment" THEN 
     INNER JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND lic.fsk18 = -1 
  WHEN "profile_comment" THEN 
    INNER JOIN eva49_lovefactory_profiles lp ON lp.user_id = a.receiver_id AND lp.status_fsk18 = -1 
  ELSE 1=1 
END 
WHERE (u.id = a.sender_id OR u.id = a.receiver_id) and (u.id <> 379) 
ORDER BY a.created_at DESC LIMIT 0, 10

有什么方法可以配对表格吗？数据库结构是这样给我的。

编辑:

有一个表“eva49_lovefactory_activity”，其中包含有关最近事件的信息。它有一列“事件”，其中包含带有事件名称的字符串(photo_add、photo_comment 等)

还有其他表 - eva49_lovefactory_photos、eva49_lovefactory_profiles、eva49_lovefactory_groups 等。在这些表中，我必须找到该项目是否已被管理员批准。如果是我可以展示它。 (fsk18 = -1)

Answer 1

将 CASE 条件移动到 JOIN 条件并执行 LEFT JOIN..

SELECT DISTINCT a.id, a.event, a.params, a.created_at, a.item_id, a.sender_id, a.receiver_id, u.name, u.id as userid 
FROM `eva49_lovefactory_activity` a, eva49_users u 

    LEFT JOIN eva49_lovefactory_photos lp ON lp.id = a.item_id AND lp.fsk18 = -1
               AND a.event='photo_add'
    LEFT JOIN eva49_lovefactory_groups lg ON lg.id = a.item_id AND lg.fsk18 = -1 
               AND a.event='group_create'
    LEFT JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND 
              lic.fsk18 = -1     AND a.event='photo_comment'
    LEFT JOIN eva49_lovefactory_item_comments lic ON lic.item_id = a.item_id AND 
              lic.fsk18 = -1     AND   a.event='profile_comment'

WHERE (u.id = a.sender_id OR u.id = a.receiver_id) and (u.id <> 379) 
ORDER BY a.created_at DESC LIMIT 0, 10