尝试使用仅在连接成功时才适用的"where"左连接 [英] Attempting left join with a 'where' that only applies if the join succeeds

问题描述

我有一些文章，存储在article表中.有些文章具有一张或多张照片，并在指定的article_id中存储在photo表中.某些照片被停用"(photo.active = '0')，不应被检索.

I have articles, stored in an article table. Some articles have one or more photos, stored in a photo table with an article_id specified. Some photos are 'deactivated' (photo.active = '0') and should not be retrieved.

我正在尝试为首页获取文章，为每篇文章提供一张照片，其中包含一张或多张照片.像这样:

I'm trying to get articles for the home page, and one photo for each article with one or more photos. Like so:

SELECT article.id, article.date, article.title, photo.filename_small 
FROM (article) 
LEFT JOIN photo ON photo.article_id=article.id 
WHERE photo.active = '1' 
GROUP BY article.id 
ORDER BY article.date desc 
LIMIT 10

(分组依据"是为了让我不会获得包含多张照片的文章的多个结果.这让我感到尴尬.)

(The "group by" is so that I don't get multiple results for articles with multiple photos. It strikes me as awkward.)

当我具有这样的WHERE photo.active = '1'时，我只会获得带有照片的结果，这使将联接变为左联接的目的达不到要求.仅当联接将带有照片的文章与之匹配时，"where"才是相关的，但是排除了所有没有活动照片的文章.有什么想法吗?

When I have the WHERE photo.active = '1' like that, I only get results with a photo, which defeats the purpose of making the join a left join. The where is only relevant if the join matches the article with a photo, but it's ruling out all articles without active photos. Any ideas?

(是的，有类似的问题，但我已经阅读了很多，但仍在努力之中.)

(Yes, there are similar questions, but I've read a lot of them and am still struggling.)

推荐答案

两个选项.

1. 将其放在join子句中:

1. Put it in the join clause:

LEFT OUTER JOIN photo ON photo.article_id=article.id AND photo.active = 1

再次明确允许null:

Explicitly allow nulls again:

WHERE (photo.active = 1 OR photo.id IS NULL)

第二个似乎不必要地复杂，因为您已经有了外部联接.我建议第一个.

The second seems unnecessarily complicated though as you already have the outer join. I'd recommend the first.

这篇关于尝试使用仅在连接成功时才适用的"where"左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！