Stack Exchange
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.

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;


Be careful on using left join, one important thing is consider the first query always as your main data or the core when using LEFT JOIN.

If I use right join it gives me ALIAS : "M" ,"M", "M", "C"
