CODEWITHSUNDEEP
mysqlleft-join
DisgruntledGoat
DisgruntledGoat

Reputation: 72580

Left Join not returning all rows

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)?

Upvotes: 26

Views: 32370

Answers (4)

JGFMK
JGFMK

Reputation: 8914

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

Upvotes: 0

Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39057

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

Upvotes: 12

MJB
MJB

Reputation: 7686

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)

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169514

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

Upvotes: 60

Related Questions