TARA TARA - 26 days ago 5
MySQL Question

MySql Inner join where data not available there but want to give condition on it

This is my tabel, relation with dependent

table data
----------------
nama | id_propinsi | id_kabupaten | id_kecamatan
A | 12 | 11 | 01
B | 13 | 01 | 01
C | 12 | 11 | 00

tabel propinsi
------------------------
id_propinsi | nama_propinsi
12 | jawa barat
13 | jawa timur

tabel kabupaten
-------------------
id_propinsi | id_kabupaten | nama_kabupaten
12 | 11 | depok
13 | 01 | magelang

tabel kecamatan
-----------------------------
id_propinsi | id_kabupaten | id_kecamatan | nama_kecamatan
12 | 11 | 01 | sawangan
13 | 01 | 01 | pasuruan

OUTPUT NOW (FALSE)

A | Jawa barat | Depok | Sawangan
B | Jawa Timur | Magelang | Pasuruan

I want to OUTPUT THIS (RIGHT)

A | Jawa barat | Depok | Sawangan
B | Jawa Timur | Magelang | Pasuruan
C | Jawa barat | Depok | -


I try to many querys but still cant get the right condition, with CASE, INNER JOIN OR ..
because id_kecamatan=00 unavailable on table kecamatan
may you know how to do it to output like that i want .. ???
Thank you so much

my query is

SELECT name, nama_propinsi, nama_kabupaten, nama_kecamatan FROM data D
INNER JOIN propinsi P ON D.id_propinsi=P.id_propinsi
INNER JOIN kabupaten K ON D.id_propinsi=P.id_propinsi AND D.id_kabupaten=K.id_kabupaten
INNER JOIN kecamatan C ON D.id_propinsi=P.id_propinsi AND D.id_kabupaten=K.id_kabupaten AND D.id_kecamatan=C.id_kecamatan

Answer

Replace all your INNER JOINs by LEFT JOIN.

And with the help of COALESCE function you can treat the NULL value as hyphen (-).

SELECT
    NAME,
    COALESCE(nama_propinsi,'-') AS prop,
    COALESCE(nama_kabupaten,'-') AS kabu,
    COALESCE(nama_kecamatan,'-') AS keca
FROM
    data D
LEFT JOIN propinsi P ON D.id_propinsi = P.id_propinsi
LEFT JOIN kabupaten K ON D.id_kabupaten = K.id_kabupaten
LEFT JOIN kecamatan C ON D.id_kecamatan = C.id_kecamatan
Comments