How do I join multiple tables in a mysql query?

Question

I already know how to join two tables or even three, but my situation is a little different....I have a table of mobile homes for sale....I have a second table of the parks those homes are in. I have a third table of bank repossesions that exist in mobile home parks. I can easily join the homes for sale with the parks, or the repos with the parks.....but how to I get mysql to combine the three. I want it to show homes for sale and the repos displayed with their corresponding parks. In other words I want to join two tables with similar data with a third table that is connected to both of the first two tables. Please help.....I was fine with the system I had until the boss now wants a single report to include both all homes for sale and all repos in their parks.....thanks!!

Answer 1

Hi,



Here I am assuming that table PARK with mobile homes is related to table MOBILEHOME. So I have joined these two tables to create a new table with alias 'pnh'. Thereafter I am joining 'pnh' with table 'BANKREPOSESSION' (alias br). All this in a single query that looks as shown below:-



SELECT br.ReposessionId,br.Amount

FROM BankReposession br INNER JOIN

(select pr.ParkId, pr.ParkName, mh.HomeId, mh.Cost FROM

Park pr INNER JOIN

MobileHome mh ON mh.ParkId=pr.ParkId) pnh ON

br.HomeId=pnh.HomeId



Hope this resolves the issue.



HAPPY CODING !!

Sujit

Answer 2

You might be over complicating it. I'll skip some basics since you seem to be connecting fine already.

I don't know all of your field names or true table names so I'll use these:

4sale, parks, repos

A query would look something like this...(it is best if they share a common field like "id")



SELECT * (replace with actual field names example: repos.address, 4sale.price, parks.city)

FROM 4sale, parks, repos

WHERE (insert your conditions here example: parks.city = $_POST['city'] ect...){assuming you're gathering the info from a form}



You can also add different SELECT commands using the UNION or UNION ALL command. (use the union all if you have fields that will contain the same value. this will allow the duplicates to show)



This would look like:

SELECT * FROM 4sale

UNION

SELECT * FROM repos

UNION

SELECT * FROM parks



Good luck, hope that helps. Feel free to update your question or send an e-mail if you get stuck!

Answer 3

SELECT [column fields that you want to retrieve]

FROM table1

INNER JOIN table2

ON table1.primarykey = table2.foreignkey

INNER JOIN table3

ON table2.primarykey = table3.foreignkey

INNER JOIN table4

ON table3.primarykey = table4.foreignkey

INNER JOIN table5

ON table4.primarykey = table5.foreignkey

WHERE field = yourcriteria



you can continue on.

primary key is the first table's key

and foreign key is the reference field



if you want to get records with no matching

records from other table you use an OUTER JOIN



LEFT OUTER JOIN table6

ON table5.primarykey = table6.foreignkey



you get the idea. ;)

Answer 4

innerjoin on mobilehome IDs