MySQL – How to SELECT from Multiple Tables Using Foreign Keys

join;MySQL

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?

Best Answer

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
Related Question