Why Does My MySQL LEFT JOIN Only Return One Row Instead of All Rows from the Primary Table?

Left Join Not Returning All Rows

Original Question:

A MySQL query involving a left outer join returns only one row instead of all rows in the primary table. The join condition includes a filter on a field in the secondary table. How can all rows from the primary table be selected, including the value from the secondary table where the field meets the filter criteria?

Solution:

The issue stems from placing the WHERE condition after the JOIN. By moving it to the JOIN clause itself, the query ensures that the join only matches rows that meet the filter criteria. Here's the modified query:

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

In this query, the WHERE condition is no longer in the separate WHERE clause after the JOIN. Instead, it has been incorporated into the ON clause of the JOIN, ensuring that only rows where pr7.field is 23 are joined. This allows all rows from jos_hp_properties to be selected, including those that do not have a match in jos_hp_properties2, which will have a NULL value for pr7.value.

The above is the detailed content of Why Does My MySQL LEFT JOIN Only Return One Row Instead of All Rows from the Primary Table?. For more information, please follow other related articles on the PHP Chinese website!