CODEWITHSUNDEEP
mysql
Biker John
Biker John

Reputation: 2711

MySQL triple join

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.

Upvotes: 23

Views: 77424

Answers (3)

Sabir Hussain
Sabir Hussain

Reputation: 1

joining three tables

i.e:

select i_f.*, i_f_d.* , i_f_c.creator_name 
  from indent_form i_f 
  join indent_form_details i_f_d on i_f.id=i_f_d.ind_id 
  join indent_form_creator i_f_c on i_f.user_id=i_f_c.id

indent_form table 1,
indent_form_details table 2,
indent_form_creator table 3,

Upvotes: -1

leonardo_assumpcao
leonardo_assumpcao

Reputation: 686

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.

Upvotes: 4

TheHe
TheHe

Reputation: 2972

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.

Upvotes: 47

Related Questions