3

I am using MySql.

I have 2 tables, one is a list of names and data with primary key propertyId.

table 2 contains images with primary key propertyImageId.

Each propertyId may have multiple images or NO images at all.

I need to get a list of all the propertyId that belongs to agentId = 1, regardless whether it has images or not.

'SELECT a.*, b.* 
FROM property a LEFT OUTER JOIN property_images b 
ON a.propertyId = b.propertyId 
INNER JOIN 
( SELECT propertyId, MAX(created) maxCreated 
FROM property_images 
GROUP BY propertyId) c 
ON b.propertyId = c.propertyId ANd b.created = c.maxCreated 
WHERE agentId = 1 ');

I'm trying a similar solution provided here MySQL INNER JOIN select only one row from second table

However, it only returns propertyId if images exist. What can I do so that it will return all the propertyId from property regardless whether property_images exist or not?

Been working on this, any help will be deeply appreciated. Thank you!!

Someone Special
  • 12,479
  • 7
  • 45
  • 76

1 Answers1

3

You can rewrite your query as below, The inner query gets single image per property id with highest created column value

SELECT 
  a.*,
  b.* 
FROM
  property a 
  LEFT JOIN 
    (SELECT 
      c.* 
    FROM
      property_images c 
      LEFT JOIN property_images d 
        ON c.propertyId = d.propertyId 
        AND c.created < d.created 
    WHERE d.propertyId IS NULL) b 
    ON a.propertyId = b.propertyId 
WHERE a.agentId = 1
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • Thank you very much! It seems to be working, except that, because both property table and property_images table i uses the field name "propertyId", the result set returns propertyId as NULL when image does not exist. – Someone Special Dec 01 '17 at 06:17
  • I fixed this by selecting fields from property_images individually without specifying the propertyId so null doesn't overwrite propertyId from property table. Thank you very much for the solution! – Someone Special Dec 01 '17 at 06:44