如何只左连接一张表?
我有这样的两个表,一个用于保存文本等,另一个用于保存图像信息,
页面表
,
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技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
在 where 子句中排除 cat_id=2 吗?
或者明确只允许 cat_id=1?
Exclude cat_id=2 in the where clause?
or explicitly allow only cat_id=1?
对此,我认为通过将 cat_id 标准添加到您的连接中,您可能会获得您正在寻找的数据:
在看到您的编辑后,我认为您需要切换 JOIN 条件。
what about this, I think that by adding the cat_id criteria to your join you may get the data you're looking for:
after seeing your edit I think you need to switch the JOIN condition.
尝试使用子查询来选择您的页面,然后向左加入您的根图像
Try using a subquery to select your pages and then left join your root images
我相信马克有正确的答案。但我也不认为你需要:
假设 img_id 是主键。
I believe Marc has the correct answer. But I also don't think you need:
Assuming img_id is a primary key.