0

I have 3 tables:

languages

id | tag   | region        | language
--   -----   -------------   --------
1    en-US   United States   English
2    es-ES   Spain           Spanish
...
modules

id | module
--   ------
1    header
2    main
3    footer
contents

id | language_id | module_id | content
--   -----------   ---------   -------
1    1             1           This is my header
2    1             3           This is my footer
3    2             1           Este es mi encabezado

I want to select all content for all of the expected modules, even if there's no contents record for that module, (notice I'm missing a "main" module) so I've written:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id

...which nicely returns

module | content
------   -------
header   This is my header
header   Este es mi encabezado
main     null
footer   This is my footer

But now I need to select only the modules with the language_id that maps to 'en-US'. If I amend my JOINs I lose my "main" module in my result:

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'en-US'
module | content
------   -------
header   This is my header
footer   This is my footer

I am very new to JOINs. What am I missing?

1

I would go with something like this that would include all records from the modules table which would include records where it doesn't match up in the contents/languages table and limit it to the language you want.

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'en-US' OR l.tag IS NULL
4
  • I posted my result, which is what you have plus I added WHERE ...OR l.tag IS NULL, which successfully filtered out non-US languages, but I'm still not sure why it works. – Phil Tune Dec 5 '19 at 18:44
  • 1
    Updated based on your feedback. – Joe W Dec 5 '19 at 18:51
  • Whilst I have you, would you know how I can have m.content return the 'en-US' version if another language is missing? (Since I built the site in English, I know I have ALL of those pieces, but i will be seeding translations from another agency and want to fallback to English when a translation is missing.) – Phil Tune Dec 6 '19 at 15:03
  • You might be able to do something using the coalesce function but I am not sure what would all be involved without having a sqlfiddle to work with. – Joe W Dec 6 '19 at 15:44
1

It's pretty obvious. You JOIN to languages filter out the null rows because there's no matching to the languages table. The combination of RIGHT JOIN and LEFT JOIN is bad, but it should work.

    SELECT m.module, m.content
    FROM `modules` m
    LEFT JOIN contents c ON m.id = c.module_id
    LEFT JOIN languages l ON c.language_id = l.id
    WHERE l.tag = 'en-US'
10
  • Why would you need to do a right join with this? – Joe W Dec 5 '19 at 18:18
  • If it's just regular JOIN, the null rows from the previous LEFT JOIN will get filter out because there's no matching c.language_id = l.id. – Eric Dec 5 '19 at 18:22
  • Weird, RIGHT JOIN ended up adding all of my languages, but when i swapped it to LEFT JOIN it seems to work. Still playing around with it to make sure. – Phil Tune Dec 5 '19 at 18:25
  • I understand that but you get to the same results without needing a right join if you ensure you are starting with the correct table in the select statement. – Joe W Dec 5 '19 at 18:28
  • @PhilTune That is because of the right join including everything from the languages table. Making into a left join would only include everything from the contents table and with a null value in languages where it doesn't match up with the contents table. – Joe W Dec 5 '19 at 18:31
0

Bingo, I figured it out but will accept @Joe W et. al. for getting me there.

SELECT m.module, m.content
FROM `modules` m
LEFT JOIN contents c ON m.id = c.module_id
LEFT JOIN languages l ON c.language_id = l.id
WHERE l.tag = 'en-US' OR l.tag IS NULL

Without the OR l.tag IS NULL I was still pulling in rows from non-US languages as well. I don't know why it works, but wanted to post it for posterity or for anyone who wants to yell at me for going about it the wrong way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.