Menu
I have this query in MySQL:
SELECT pr.*, pr7.value AS `room_price_high`
FROM `jos_hp_properties` pr
LEFT OUTER JOIN `jos_hp_properties2` pr7 ON pr7.property=pr.id
WHERE pr7.field=23
The jos_hp_properties table has 27 rows but the query only returns one. Based on this question I think it may be because of the WHERE clause. The jos_hp_properties2 table has fields id, property, field, value, where field is a foreign key to a third table (which I don't need to get data from).
Is there a way to select all the rows from the first table, including the value from table #2 where the field is 23 (or NULL if there is no field 23)?
658
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table; the join will still return a row in the result, but with NULL in each column from the right table.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2).
Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
The JOIN or INNER JOIN does not return any non-matching rows at all. It returns only the rows that match in both of the tables you join. If you want to get any unmatched rows, you shouldn't use it. The LEFT JOIN and the RIGHT JOIN get you both matched and unmatched rows.
Sure. Move the WHERE condition to the JOIN:
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
You must place the pr7 criteria in the join, not in the where clause. The where clause works on the entire result set AFTER the join has been performed.
SELECT pr.*, pr7.value AS `room_price_high`
FROM `jos_hp_properties` pr
LEFT OUTER JOIN `jos_hp_properties2` pr7 ON pr7.property=pr.id and pr7.field=23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
