Thursday, 30 August 2018

MySQL Left Join does not work as I expected

Table Name : Feature

+--------+----------+
| fea_id | fea_name |
+--------+----------+
|      1 | Price    |
|      2 | Height   |
|      3 | Weight   |
+--------+----------+

Table Name : property_meta
+----+--------+--------+-------+
| id   | fea_id | pro_id | value |
+----+--------+--------+-------+
|  100 |      1 |    300 |  2500 |
|  200 |      2 |    300 |   300 |
|
+----+--------+--------+-------+

My Query
SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id where property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC
Expected Result
+--------+--------+-------+
| fea_id | pro_id | value |
+--------+--------+-------+
|      1 |    300 | 2500  |
|      2 |    300 | 300   |
|      3 |    300 | NULL  |
+--------+--------+-------+

But I am getting without the last rowI need that last row also. How I modify my query to get the last row also?
That means I need to get all rows of Feature table even there is no value in property meta table.

where property_meta.pro_id=300 makes your left join to an inner join. Add this to the on clause and it is working:
SELECT * FROM feature LEFT JOIN property_meta ON feature.fea_id = property_meta.fea_id and property_meta.pro_id=300 GROUP by feature.fea_id ORDER BY feature.fea_id ASC

0 comments:

Post a Comment