How to SELECT from three different tables using multiple foreign keys

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 2

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'

Method 3

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply