Left Join not returning all rows

I have this query in MySQL:

SELECT pr.*, pr7.value AS `room_price_hi开发者_StackOverflow社区gh`
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)?

---

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

---

Try this:

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 OR pr7.field is null)

---

You can also use a CTE (Common Table Expression) to do the select, then use the CTE to do the left join..

wrc (parentid, childid) as (
    select parentid, childid
    from placechild
    where relationshipid in  (select id from placerelationship where relationship = 'Winter Region Capital')
),
stw (cnid, coid, capid, st_or_te, sid, scid,wcid) as (
    select s.cnid, s.coid, s.capid, s.st_or_te, s.sid, s.scid, w.childid
    from stcap s
    left join wrc w
    on s.sid = w.parentid
)
select * from stw