Question:

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.)

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.)

Two options.

<ol><li>

Put it in the join clause:

LEFT OUTER JOIN photo ON photo.article_id=article.id AND photo.active = 1 </li> <li>

Explicitly allow nulls again:

WHERE (photo.active = 1 OR photo.id IS NULL) </li> </ol>

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

Try something like

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