MYSQL one to many relationship query returning a single row from 'many' table

Question

I have 2 mysql tables namely 'properties' and 'images'.

          Properties
  +------------+---------------+
  | propertyId | propertyTitle |  
  +------------+---------------+
  |          1 | 1 acre land   |  
  |          2 | 2 acre land   |  
  |          3 | 3 acre land   |  
  +------------+---------------+

                Images
  +---------+---------------+---------------+
  | imageId |     image     |  propertyId   |  
  +---------+---------------+---------------+
  |       1 | land.jpeg     |        1      |
  |       2 | landview.jpg  |        1      |
  |       3 | viewland.jpeg |        2      |
  +---------+---------------+---------------+

They have a one-many relationship and the images table has a foreign key of the properties table. I would like to perform a query that selects a property and then selects a single image of the property. The query i'm trying to work with is

   SELECT * FROM properties p
    JOIN (SELECT * FROM images im LIMIT 1)
    ON im.propertyId = p.propertyId;

Its not working though. Neither does it return an error.

Answer 1

We can handle this problem using two joins. First, join the properties table to the images table as you suspected in your question. Then, do an additional to join to a subquery which finds the first image for each property.

SELECT
    p.propertyId,
    p.propertyTitle,
    i1.image
FROM properties p
INNER JOIN images i1
    ON p.propertyId = i1.propertyId
INNER JOIN
(
    SELECT propertyId, MIN(imageId) AS min_imageId
    FROM images
    GROUP BY propertyId
) i2
    ON i1.propertyId = i2.propertyId AND
       i1.imageId = t2.min_imageId;