Why Does My SQL Left Join Only Return One Row Instead of All Rows?

SQL Query Not Returning all Rows with Left Join

This query aims to retrieve all rows from the jos_hp_properties table (shortened as pr) along with the value from the jos_hp_properties2 table (shortened as pr7), where the field named field equals 23 in the latter table. However, the current query is only returning a single row, despite the jos_hp_properties table containing 27 rows.

To address this issue, the WHERE clause should be moved to the JOIN statement, as follows:

SELECT pr.*, pr7.value AS `room_price_high`
FROM `jos_hp_properties` pr
LEFT JOIN `jos_hp_properties2` pr7 
ON pr7.property=pr.id
AND 
pr7.field=23

With this modified query, the WHERE clause no longer restricts the result set prior to the join. Instead, rows from the jos_hp_properties table will be included even if there is no corresponding row in the jos_hp_properties2 table for field 23 (in which case the returned value will be NULL). This ensures that all rows from the first table are returned, along with the desired data from the second table, where applicable.

The above is the detailed content of Why Does My SQL Left Join Only Return One Row Instead of All Rows?. For more information, please follow other related articles on the PHP Chinese website!