Return all data from 1st Table, and only 1 data from 2nd Table if exist

Question

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!!

Answer 1

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