mysql - 过滤mysql连接表中的部分数据

Question

这是我的初始 mysql 代码，它显示了我需要的内容:

    SELECT (
    CASE 
       WHEN (c.`norm_id` = 71) THEN 71
       ELSE c.parent_id
    END) AS parent, 
    SUM(d.number) AS parent_sum,
    COUNT(DISTINCT(c.norm_id)) AS child_cnt
FROM un_catalog AS c
    LEFT JOIN un_data AS d ON (c.norm_id =  d.type2_norm_id)
    LEFT JOIN exp_weblog_titles AS wt USING (entry_id)
WHERE (c.parent_id != '') AND (c.`type_id` = 2) OR (c.norm_id = 71) 
GROUP BY parent
ORDER BY parent ASC;

现在我添加一个过滤器:

 SELECT (
    CASE 
       WHEN (c.`norm_id` = 71) THEN 71
       ELSE c.parent_id
    END) AS parent, 
    SUM(d.number) AS parent_sum,
    COUNT(DISTINCT(c.norm_id)) AS child_cnt
FROM un_catalog AS c
    LEFT JOIN un_data AS d ON (c.norm_id =  d.type2_norm_id)
    LEFT JOIN exp_weblog_titles AS wt USING (entry_id)
WHERE (c.parent_id != '') AND (wt.year = 2014) AND (c.`type_id` = 2) OR (c.norm_id = 71) 
GROUP BY parent
ORDER BY parent ASC;

我需要过滤第二个表(第 2 列)中的数据，同时保持结构(第 1 列和第 3 列)而不进行过滤。我该怎么做？

Answer 1

外连接时，不要将外表条件放在WHERE子句中，而将then放在ON子句中。 (否则外连接将用作常规内连接...)

 SELECT (
    CASE 
       WHEN (c.`norm_id` = 71) THEN 71
       ELSE c.parent_id
    END) AS parent, 
    SUM(d.number) AS parent_sum,
    COUNT(DISTINCT(c.norm_id)) AS child_cnt
FROM un_catalog AS c
    LEFT JOIN un_data AS d ON (c.norm_id = d.type2_norm_id)
    LEFT JOIN exp_weblog_titles AS wt ON wt.entry_id = XXX.entry_id AND (wt.year = 2014)
WHERE (c.parent_id != '')  AND (c.`type_id` = 2) OR (c.norm_id = 71) 
GROUP BY parent
ORDER BY parent ASC;

注意，我不知道entry_id属于哪个表，所以将上面的XXX替换为c或d! (使用...)