LEFT JOIN with MIN() - Please help

Databases
1

Hi There,

I just can not get my head around this, so any help is appreciated.

I am building a real estate web site and need to look up properties to list them.

I need to get an image from the jos_mh_images table if one exists. If not - I still want to get the content from jos_mh_properties - thus the LEFT join.

Here is a simplified version of my query:


SELECT p.*, 									 MIN(i.image_name_id), i.image_ext
FROM jos_mh_properties AS p
LEFT JOIN jos_mh_images AS i ON p.id = i.property_id
WHERE p.published = 1
GROUP BY p.id
LIMIT 0, 10

My issue is that the query returns the correct MIN i.image_name_id but the returned i.image_ext is not from the same row.

I would like to select the whole row from jos_mh_images where i.image_name_id is smallest and property_id = p.id

My original query has lots of other LEFT JOINS that works great - they have been removed to make the question easier to understand.

Please help me :slight_smile:

2

i’m in a hurry, so i can only point you to an example. you want something like this: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

if that doesn’t work out for you, i’ll probably be able to write the query for you tomorrow.

3

Thanks for trying but I am sorry - I just don’t get it, and I have forgotten a line in the example above I see. The real query looks like this:

SELECT p.*,
MIN(i.image_name_id), i.image_ext
FROM jos_mh_properties AS p
LEFT JOIN jos_mh_images AS i ON p.id = i.property_id
WHERE p.published = 1
GROUP BY p.id
LIMIT 0, 10

Actually the original query has this as well but line two is all the way at the right and easily missed.

Anyway - thanks again - I hope you can find the time to enlighten me.

4

one thing you should be on the lookout for – if you ever find yourself using the dreaded, evil “select star” and a GROUP BY clause at the same time, you are almost certainly doing something wrong…

note too that LIMIT without ORDER BY doesn’t make much sense

here’s your query:

SELECT p.*
     , i.image_name_id
     , i.image_ext
  FROM jos_mh_properties AS p
LEFT OUTER
  JOIN jos_mh_images AS i
    ON i.property_id = p.id
   AND i.image_name_id =
       ( select min(image_name_id)
           from jos_mh_images
          where property_id = p.id )
 WHERE p.published = 1
5

Wow - works like a charm - I am sure this was a piece of cake for you, but I am dazzled anyway! Where do I sign up for the fan club?

There will be an ORDER BY in the statement.

I am not sure why I would be doing something wrong if I use the * and GROUP BY at the same time - could you explain why (If you have time - I am just curious)?

6

it’s a long story…

start here: GROUP BY and HAVING with Hidden Fields

it’s almost a certainty that the “select star” will invoke many columns that are not included in the GROUP BY

these are the so-called hidden columns, and if they are not unique within each group, then the results are unpredictable

avoid unpredictability!! write valid GROUP BY clauses!!

:wink: