如何只左连接一张表?

发布于 2024-11-26 19:25:39 字数 1671 浏览 1 评论 0原文

我有这样的两个表,一个用于保存文本等,另一个用于保存图像信息,

页面表

pg_id     pg_content    parent_id 
10        xxx           3         
11        xxx           3  
12        xxx           3

图像表

img_id    pg_id   img_order     cat_id
1         10      1             1
2         10      1             2
3         10      2             2
4         11      1             1

所以我想列出所有仅包含来自 cat_id 1 的 img_order 1 图像的页面,但是当页面包含 cat_id 2 中的图像时,我会得到重复的行,

SELECT*
FROM root_pages

LEFT JOIN root_images_pages
ON root_images_pages.pg_id = root_pages.pg_id

WHERE root_pages.parent_id = '3'
AND root_pages.pg_id != '3'
AND (root_images_pages.img_order = '1' OR  root_images_pages.img_id is NULL)

ORDER BY root_pages.pg_created DESC
LIMIT 12

我该如何解决修复它吗?

我想要的结果是,

pg_id     pg_content    parent_id     img_id    pg_id   img_order     cat_id    
    10        xxx           3          1         10      1             1         
    11        xxx           3          4         11      1             1  
    12        xxx           3          null      null    null          null

编辑:

我必须将此行添加到查询中,否则无法列出没有图像的页面,

AND (i.img_order = '1' OR i.img_id is NULL)

所以这里是,

SELECT*
FROM root_pages p

LEFT JOIN root_images_pages i
ON i.pg_id = p.pg_id and i.cat_id = 1

WHERE p.parent_id = '3'
AND p.pg_id != '3'
AND p.pg_hide != '1'
AND (i.img_order = '1' OR i.img_id is NULL)

ORDER BY p.pg_created DESC
LIMIT 12

I have this kind of two tables, one is for keeping text, etc, another one is to keep image info,

pages table,

pg_id     pg_content    parent_id 
10        xxx           3         
11        xxx           3  
12        xxx           3

image table,

img_id    pg_id   img_order     cat_id
1         10      1             1
2         10      1             2
3         10      2             2
4         11      1             1

So I want to list all the pages with the img_order 1 image from cat_id 1 only, but I get duplicated rows when a page has images in cat_id 2,

SELECT*
FROM root_pages

LEFT JOIN root_images_pages
ON root_images_pages.pg_id = root_pages.pg_id

WHERE root_pages.parent_id = '3'
AND root_pages.pg_id != '3'
AND (root_images_pages.img_order = '1' OR  root_images_pages.img_id is NULL)

ORDER BY root_pages.pg_created DESC
LIMIT 12

How can I fix it?

I result I want is,

pg_id     pg_content    parent_id     img_id    pg_id   img_order     cat_id    
    10        xxx           3          1         10      1             1         
    11        xxx           3          4         11      1             1  
    12        xxx           3          null      null    null          null

EDIT:

I have to add this line below to the query otherwise the page with no images cannot be listed,

AND (i.img_order = '1' OR i.img_id is NULL)

So here it is,

SELECT*
FROM root_pages p

LEFT JOIN root_images_pages i
ON i.pg_id = p.pg_id and i.cat_id = 1

WHERE p.parent_id = '3'
AND p.pg_id != '3'
AND p.pg_hide != '1'
AND (i.img_order = '1' OR i.img_id is NULL)

ORDER BY p.pg_created DESC
LIMIT 12

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

甜心 2024-12-03 19:25:39

在 where 子句中排除 cat_id=2 吗?

WHERE .... AND (root_images_pages.cat_id <> 2)

或者明确只允许 cat_id=1?

WHERE .... AND (root_images_pages.cat_id = 1)

Exclude cat_id=2 in the where clause?

WHERE .... AND (root_images_pages.cat_id <> 2)

or explicitly allow only cat_id=1?

WHERE .... AND (root_images_pages.cat_id = 1)
无尽的现实 2024-12-03 19:25:39

对此,我认为通过将 cat_id 标准添加到您的连接中,您可能会获得您正在寻找的数据:

SELECT*
FROM root_pages r

LEFT JOIN root_images_pages i
ON i.pg_id = r.pg_id and i.cat_id = 1

WHERE r.parent_id = '3'

ORDER BY r.pg_created DESC
LIMIT 12

在看到您的编辑后,我认为您需要切换 JOIN 条件。

LEFT JOIN root_images_pages i
ON r.pg_id = i.pg_id and i.cat_id = 1

what about this, I think that by adding the cat_id criteria to your join you may get the data you're looking for:

SELECT*
FROM root_pages r

LEFT JOIN root_images_pages i
ON i.pg_id = r.pg_id and i.cat_id = 1

WHERE r.parent_id = '3'

ORDER BY r.pg_created DESC
LIMIT 12

after seeing your edit I think you need to switch the JOIN condition.

LEFT JOIN root_images_pages i
ON r.pg_id = i.pg_id and i.cat_id = 1
空城仅有旧梦在 2024-12-03 19:25:39

尝试使用子查询来选择您的页面,然后向左加入您的根图像

SELECT
    *
FROM
    (SELECT
        *
    FROM 
        root_images_pages
    WHERE
        cat_id = 1
    ) AS i
    LEFT JOIN root_pages AS p
    ON i.pg_id = p.pg_id

Try using a subquery to select your pages and then left join your root images

SELECT
    *
FROM
    (SELECT
        *
    FROM 
        root_images_pages
    WHERE
        cat_id = 1
    ) AS i
    LEFT JOIN root_pages AS p
    ON i.pg_id = p.pg_id
后来的我们 2024-12-03 19:25:39

我相信马克有正确的答案。但我也不认为你需要:

OR  root_images_pages.img_id is NULL

假设 img_id 是主键。

I believe Marc has the correct answer. But I also don't think you need:

OR  root_images_pages.img_id is NULL

Assuming img_id is a primary key.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文