MySQL triple join

Question

Im having a hard time building the triple join query.

I have three tables:

House:

house ID(key) | Address | personID |

Person:

personID (key) | Name | Address | 

Images: // of house

imageID (key) | personID | url | 

I would like to limit the results to 5.

I want to query house address-es (main), it's owner name & address, and one picture of the owner.

Note: There are up to 3 images of every person (3 rows in image table), but just one is necessary, doesnt matter which one.

Answer 1

SELECT h.Address, p.Name, p.Address as OwnerAddress, i.url FROM house AS h
INNER JOIN person AS p ON p.personID = h.personID
INNER JOIN images AS i ON i.personID = p.personID
GROUP BY h.houseID

should work for you.

Answer 2

Maybe it'd better for you to use LEFT OUTER JOIN, something like

SELECT
   h.Address  as `h_address`,
   p.Name     as `p_name`,
   p.Address  as `p_address`,
   i.url      as `i_url`
FROM house AS `h`
   LEFT OUTER JOIN person AS `p` ON (p.personID = h.personID)
   LEFT OUTER JOIN images AS `i` ON (p.personID = i.personID)
GROUP BY h.houseID

It would display also houses with no registered images.