如何左联接另一个嵌套表 - How to left join another nested table?

How to left join another nested table?

本文关键字：嵌套另一个何左联 | 更新日期: 2023-09-27

如何左加入另一个嵌套表?

第一个查询

SELECT *
FROM category AS a
WHERE a.type = 'content'

结果

category_id  type       code
1            content    content_1
2            content    content_2
3            content    content_3

第二次查询,

SELECT*
FROM content AS c
LEFT JOIN article_has_content AS x
ON x.content_id = c.content_id
WHERE x.article_id = '4'

结果

category_id  value       article_id
1            xxa         4
2            xxb         4

我所追求的结果，

category_id  type       code          value
1            content    content_1     xxa
2            content    content_2     xxb
3            content    content_3     NULL

第三个查询

SELECT*
FROM 
(
    SELECT *
    FROM category AS a
    WHERE a.type = 'content'
) a
LEFT JOIN b
(
    SELECT*
    FROM content AS c
    LEFT JOIN article_has_content AS x
    ON x.content_id = c.content_id
    WHERE x.article_id = '4'
) b
ON b.category_id = a.category_id

错误,

1064 -你有一个错误的SQL语法;查看与MySQL服务器版本对应的手册，以获得使用
的正确语法。
附近"(SELECT *FROM content AS c
LEFT JOIN article_has_content AS x
ON x.content' at line 9

你差一点就对了:

SELECT *
FROM 
(
    SELECT *
    FROM category AS a
    WHERE a.type = 'content'
) a
LEFT JOIN                              -- the alias name here was wrong
(
    SELECT*
    FROM content AS c
    LEFT JOIN article_has_content AS x
    ON x.content_id = c.content_id
    WHERE x.article_id = '4'
) b                                    -- and you had the alias already here at the right position
ON b.category_id = a.category_id

将别名放在子查询后面。但一般来说，你的查询过于复杂了。我认为这是你想要的:

SELECT c.category_id, c.type, c.code, con.value
FROM category c left join
     content con
     on c.category_id = con.category_id left join
     article_has_content ahc
     on con.content_id = ahc.content_id and
        ahc.article_id = '4';

如何将这两个查询组合起来，如下所示:

SELECT*
FROM content AS c
LEFT JOIN article_has_content AS x
ON x.content_id = c.content_id
LEFT JOIN category a ON a.category_id =  c.category_id AND a.type = 'content'
WHERE x.article_id = '4'