Home / Questions / 72387
Accepted
albertcito
Asked: 2020-05-24 07:33:53 +0800 CST 2020-05-24 07:33:53 +0800 CST

Get all results from tables, even those that are not joined

  • 772

I need help with the following query in MySQL, I have 3 tables:

app :

+--------------+-----------+
| id_app  (PK) | name      |
+--------------+-----------+
| 1            | Facebook  |
| 2            | Instagram |
+--------------+-----------+

lang :

+--------------+-----------+
| id_lang (PK) | value     |
+--------------+-----------+
| 1            | English   |
| 2            | Español   |
+--------------+-----------+

app_lang :

+--------------+-------------+
| id_app  (PK) | id_lang (PK) |
+--------------+-------------+
| 1            | 1           |
| 2            | 1           |
+--------------+-------------+

What I want is that when I review the app (I have the id_app) it brings me all the languages ​​even if they are not associated in the app_lang table to the id_appcorresponding one

Result :

+----------+----------------------+---------+
| id_app   | id_lang | name       | value   | 
+----------+---------+------------+---------+
| 1        | 1       | Facebook   | English |
| NULL     | 2       | NULL       | Español |
+---------+----------+------------+---------+
mysql

1 Answers

  1. Best Answer

    You just have to use LEFT JOIN:

    SELECT  a.id_app,
        l.id_lang,
        a.name,
        l.value
FROM lang l
LEFT JOIN app_lang al
    ON l.id_lang = al.id_lang
LEFT JOIN app a
    ON al.id_app = a.id_app
    AND a.id_app = 1
;
    • 1