Fadly Dzil Fadly Dzil - 1 month ago 14
MySQL Question

Return 0 in left join command based another select left join

I have a query like this :

SELECT a.ID_ITEM_INSPECTION, a.NOMOR_ITEM_INSPECTION, a.NAMA_ITEM_INSPECTION,
(SELECT d.NO_INSPECTION FROM tb_master_inspection_report d WHERE d.NO_INSPECTION = 66) AS NO_INSPECTION,
b.ID_ITEM_INSPECTION, b.CONDITION, b.PCS, b.`ALIAS`
FROM `db_depo`.`tb_item_inspection_report` a

LEFT JOIN (SELECT b.CONDITION, b.ID_ITEM_INSPECTION, b.PCS, c.ALIAS
FROM tb_detail_inspection_report b
LEFT JOIN tb_item_condition_detail_inspection_report c
ON b.CONDITION = c.ID_ITEM
WHERE b.NO_INSPECTION = 66) b
ON b.ID_ITEM_INSPECTION = a.ID_ITEM_INSPECTION
WHERE a.TIPE = 'T'
ORDER BY a.NOMOR_ITEM_INSPECTION ASC LIMIT 1000;


It gives me a table like this :

+--------------------+-----------------------+---------------------------------+---------------+--------------------+-----------+------+-------+
| ID_ITEM_INSPECTION | NOMOR_ITEM_INSPECTION | NAMA_ITEM_INSPECTION | NO_INSPECTION | ID_ITEM_INSPECTION | CONDITION | PCS | ALIAS |
+--------------------+-----------------------+---------------------------------+---------------+--------------------+-----------+------+-------+
| 1 | 1 | Protection Box Cover | 66 | NULL | NULL | NULL | NULL |
| 2 | 2a | Manlid | 66 | NULL | NULL | NULL | NULL |
| 3 | 2b | Swing bolts | 66 | NULL | NULL | NULL | NULL |
| 28 | 2c | Manlid Gasket | 66 | NULL | NULL | NULL | NULL |
| 4 | 3a | PV Valve / Flame Trap / Gauge | 66 | NULL | NULL | NULL | NULL |
| 5 | 3b | Rupture Disc | 66 | 5 | 6 | 1 | M |
| 6 | 4a | Top Outlet (Flange/Bolt/Nut) | 66 | NULL | NULL | NULL | NULL |
| 12 | 4b | Syphone Tube/Butterfly | 66 | 12 | 6 | 1 | 0 |
| 7 | 5 | Top Operated Valve | 66 | 7 | 6 | 1 | 0 |
| 8 | 6 | Dipstick | 66 | 8 | 3 | 1 | C |
| 9 | 7 | Air Line Valve (Ball Butterfly) | 66 | NULL | NULL | NULL | NULL |
| 10 | 8 | Calibration Chart | 66 | NULL | NULL | NULL | NULL |
| 11 | 9 | Walkway | 66 | NULL | NULL | NULL | NULL |
+--------------------+-----------------------+---------------------------------+---------------+--------------------+-----------+------+-------+
13 rows in set (0.00 sec)


Please see, there is a record with value 0,

But, if I separate the left join command in :

SELECT b.CONDITION, b.ID_ITEM_INSPECTION, b.PCS, c.ALIAS
FROM tb_detail_inspection_report b
LEFT JOIN tb_item_condition_detail_inspection_report c
ON b.CONDITION = c.ID_ITEM
WHERE b.NO_INSPECTION = 66


+-----------+--------------------+------+-------+
| CONDITION | ID_ITEM_INSPECTION | PCS | ALIAS |
+-----------+--------------------+------+-------+
| 6 | 5 | 1 | M |
| 6 | 12 | 1 | M |
| 6 | 7 | 1 | M |
| 3 | 8 | 1 | C |
| 6 | 23 | 1 | M |
+-----------+--------------------+------+-------+
5 rows in set (0.00 sec)


What just happened ?
Any help or suggestion is so appreciated.

Answer

If you use right join like this:

   SELECT a.ID_ITEM_INSPECTION, a.NOMOR_ITEM_INSPECTION, a.NAMA_ITEM_INSPECTION,
    (SELECT d.NO_INSPECTION FROM tb_master_inspection_report d WHERE d.NO_INSPECTION = 66) AS NO_INSPECTION,
     b.ID_ITEM_INSPECTION, b.CONDITION, b.PCS, b.`ALIAS`
     FROM `db_depo`.`tb_item_inspection_report` a

right JOIN (SELECT b.CONDITION, b.ID_ITEM_INSPECTION, b.PCS, c.ALIAS
                                                FROM tb_detail_inspection_report b
                                                leftJOIN tb_item_condition_detail_inspection_report c
                                                ON b.CONDITION = c.ID_ITEM
                                                WHERE b.NO_INSPECTION = 66) b
ON b.ID_ITEM_INSPECTION = a.ID_ITEM_INSPECTION
WHERE a.TIPE = 'T'
ORDER BY a.NOMOR_ITEM_INSPECTION ASC LIMIT 1000;