TARA TARA - 2 days ago 3
MySQL Question

How to make foreach in database mysql

This is my tabel, relation with dependent


Table: data_kel


+---------+--------------+
| alamat | nomor_kk |
+---------+--------------+
| tes1 | 11 |
+---------+--------------+
| tes2 | 12 |
+---------+--------------+
| tes3 | 12 |
+---------++-------------+
| tes4 | 12 |
+---------+--------------+
| tes5 | 11 |
+---------+--------------+



Table: data_mustahik


+---------+---------------+------------+
| nama | nomor_kk | kepala_kel |
+---------+---------------+------------+
| 11 | 11 | 1 |
+---------+--------------+-------------+
| 12 | 12 | 1 |
+---------+--------------+-------------+
| 13 | 12 | 0 |
+---------++---------------+-----------+
| 14 | 12 | 0 |
+---------+--------------+-------------+
| 15 | 11 | 0 |
+---------+--------------+-------------+



Table: data_survey


+--------------+---------------+
| id_survey | nomor_kk |
+--------------+---------------+
| 21 | 11 |
+--------------+---------------+
| 22 | 12 |
+--------------+---------------+
| 23 | 12 |
+--------------+---------------+
| 24 | 12 |
+--------------+---------------+
| 25 | 11 |
+--------------+---------------+



Table: rekapitulasi_kelayakan


+--------------+---------------+
| id_srv | kelayakan |
+--------------+---------------+
| 21 | layak |
+--------------+---------------+
| 22 | tidak |
+--------------+---------------+
| 23 | - |
+--------------+---------------+
| 24 | - |
+--------------+---------------+
| 25 | - |
+--------------+---------------+


OUTPUT NOW (FALSE)

11 | 11 | Layak
11 | 11 | Layak
12 | 12 | Tidak
12 | 12 | Tidak
12 | 12 | Tidak
13 | 12 | Tidak
13 | 12 | Tidak
13 | 12 | Tidak
14 | 12 | Tidak
14 | 12 | Tidak
14 | 12 | Tidak
15 | 11 | Layak
15 | 11 | Layak


I want to OUTPUT THIS (RIGHT)

11 | 11 | Layak
12 | 12 | Tidak
13 | 12 | Layak
14 | 12 | Layak
15 | 11 | Tidak


I'm stuck to query this .. Actually this query so long .. its just simple ..
If i using group .. the output just one not all appear ..

i want to grouping
nomor_kk 11 with nama 11,15 => layak
nomor_kk 12 with nama 12,13,14 => tidak


my query is

SELECT * FROM data_mustahik dk
INNER JOIN data_survey ds
ON dk.nomor_kk=ds.nomor_kk
INNER JOIN rekapitulasi_kelayakan rk
ON ds.id_survey=rkid_srv


QUery

CREATE TABLE data_kel
(`alamat` varchar(5), `nomor_kk` int)
;

INSERT INTO data_kel
(`alamat` , `nomor_kk`)
VALUES
('tes1', 11),
('tes2', 12),
('tes3', 12),
('tes4', 12),
('tes5', 11)
;

CREATE TABLE data_survey
(`id_survey` int, `nomor_kk` int)
;

INSERT INTO data_survey
(`id_survey`, `nomor_kk`)
VALUES
(21, 11),
(22, 12),
(23, 12),
(24, 12),
(25, 11)
;




CREATE TABLE rekapitulasi_kelayakan
(`id_srv` int, `kelayakan` varchar(5))
;

INSERT INTO rekapitulasi_kelayakan
(`id_srv`, `kelayakan`)
VALUES
(21, 'layak'),
(22, 'tidak'),
(23, '-'),
(24, '-'),
(25, '-')
;


CREATE TABLE data_mustahik
(`nama` int, `nomor_kk` int, `kepala_kel` int)
;

INSERT INTO data_mustahik
(`nama`, `nomor_kk`, `kepala_kel`)
VALUES
(11, 11, 1),
(12, 12, 1),
(13, 12, 0),
(14, 12, 0),
(15, 11, 0)
;


Query 2

SELECT dk.nama,dl.nomor_kk,rk.kelayakan FROM data_mustahik dk
INNER JOIN data_kel dl
ON dk.nomor_kk=dl.nomor_kk
INNER JOIN data_survey ds
ON dk.nomor_kk=ds.nomor_kk
INNER JOIN rekapitulasi_kelayakan rk
ON ds.id_survey=rk.id_srv
WHERE rk.kelayakan != '-'
order by nama

Answer

Your second query correctly outputs 13 rows. To get your desired output you either need to drop the join to data_kel (which doesn't seem to be needed) or decide which of the duplicate data_kel.nomor_kk's you want from this table or use a distinct clause like so

SELECT distinct dk.nama,dl.nomor_kk,rk.kelayakan FROM data_mustahik dk
INNER JOIN data_kel dl ON dk.nomor_kk=dl.nomor_kk
INNER JOIN data_survey ds ON dk.nomor_kk=ds.nomor_kk
INNER JOIN rekapitulasi_kelayakan rk ON ds.id_survey=rk.id_srv
WHERE rk.kelayakan != '-'
order by nama
;
Comments