MySQL join with LIMIT 1 from two tables

Question

I have two tables. One with information about properties. The other stores 3 images for each property. Of these three images - one is marked as being the "main" image.

So I have:

Properties:
p_id    name
1   villa a
2   villa b
3   villa c
4   villa d

and

    Images
i_id p_id main
1    1     0
2    1     0
3    1     1
4    2     0
5    2     1
6    2     0

I need to produce a query which returns all of the properties with the id of their main image. e.g.

p_id  name    i_id
1     villa a  3
2     villa b  5

I know this will involve using LIMIT 1 and a join, but not sure where to start, I have already attempted doing this by using a subquery but felt it must be less complicated than what I was doing....

* HOW DO I * Make it so it orders the query by "main" selecting the top 1 (i.e. so if main is not set it will still select an image) ?

Answer 1

select p.p_id, p.name, i.i_id 
from properties p join images i on p.p_id = i.p_id 
where i.main=1

Answer 2

Here you go:

SELECT p_id, name, i_id
FROM properties p INNER JOIN images i ON (p.p_id = i.p_id AND i.main = 1)

or

SELECT p_id, name, i_id
FROM properties p INNER JOIN images i ON (p.p_id = i.p_id)
WHERE i.main = 1

Answer 3

You want to use a query like the following:

SELECT p.p_id, p.name, i.i_id FROM Images i INNER JOIN Properties p ON p.p_id = i.p_id WHERE i.main = 1

Answer 4

Here's one way:

select      *
from        properties p
left join   images i 
on          p.p_id = i.p_id 
and         i.main = 1

The left join will return a NULL image if no main image is found.