Left Join not returning all rows

mysql left-join
DisgruntledGoat picture DisgruntledGoat · Apr 23, 2010 · Viewed 15.6k times · Source

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

Answer

mechanical_meat picture mechanical_meat · Apr 23, 2010

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

Related questions

Insert using LEFT JOIN and INNER JOIN

Hey all i am trying to figure out how to go about inserting a new record using the following query: SELECT user.id, user.name, user.username, user.email, IF(user.opted_in = 0, 'NO', 'YES') AS optedIn FROM user LEFT …

Read More
LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id | …

Read More
How to use mysql JOIN without ON condition?

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.

Read More