CODEWITHSUNDEEP
mysql
TARA
TARA

Reputation: 527

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           |
+---------+--------------+---------------+--------------+

Table: propinsi

+--------------+---------------+
| id_propinsi  | nama_propinsi |
+--------------+---------------+
| 12           | jawa barat    |
+--------------+---------------+
| 13           | jawa timur    |
+--------------+---------------+

Table: kabupaten

+--------------+---------------+---------------+
| id_propinsi  | id_kabupaten  | nama_kabupaten|
+--------------+---------------+---------------+
| 12           | 11            | depok         |
+--------------+---------------+---------------+
| 13           | 01            | magelang      |
+--------------+---------------+---------------+

Table: 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

Upvotes: 1

Views: 46

Answers (1)

1000111
1000111

Reputation: 13519

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

Upvotes: 3

Related Questions