A fiddle of the question below can be found here.
I'm attempting to get a list of all articles regardless of their joined association.
Let's say we have the following tables:
articles
| id | title |
|---|---|
| 1 | Article 1 |
| 2 | Article 2 |
| 3 | Article 3 |
article_translations
| id | article_id | language |
|---|---|---|
| 1 | 1 | nl-NL |
| 2 | 2 | en-GB |
Notice there is no entry for article_translations.article_id = 3.
The goal is to look up an article's associated language and also return the article record even if it's missing an entry in article_translations.
The query I'm using now is the following:
SELECT
articles.id,
MAX(article_translations.language)
FROM articles
LEFT JOIN
article_translations ON article_translations.article_id = articles.id
WHERE (
article_translations.language = 'nl-NL'
or (
article_translations.language IS NULL
)
)
GROUP BY articles.id
The expected result would be:
| id | language |
|---|---|
| 1 | NULL |
| 2 | en-GB |
| 3 | NULL |
The actual result is:
| id | language |
|---|---|
| 2 | en-GB |
| 3 | NULL |
This issue seems to be caused by the article_translations table actually containing a valid join for the desired record, but the where-clause seems to filter it out somehow.
If we change the query to look up nl-NL in stead of en-GB, we find the previously missing record, but now miss the previously found record.
| id | language |
|---|---|
| 1 | nl-NL |
| 3 | NULL |
Does anyone have any clue what's actually going on and how to get the results I need?
--- UPDATE ---
Thanks @Bill Karwin for pointing me in the right direction. Based on his left outer join I managed to create another query that returns the results that I expected:
SELECT
articles.id,
MAX(article_translations.language)
FROM articles
LEFT OUTER JOIN
article_translations ON article_translations.article_id = articles.id
AND (
article_translations.language = 'en-GB'
OR (
article_translations.language IS NULL
)
)
GROUP BY articles.id
I am still wondering however, why the left outer join combined with a clause returns the expected results, but when using the same join, combined with a where clause returns different results:
left outer join + where clause fiddle
SELECT
articles.id,
MAX(article_translations.language)
FROM articles
LEFT OUTER JOIN
article_translations ON article_translations.article_id = articles.id
WHERE (
article_translations.language = 'en-GB'
OR (
article_translations.language IS NULL
)
)
GROUP BY articles.id
So the question that remains is: is it possible to have a join (without a clause), combined with a where that is able to returns all article's in the set.
