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!!

sujimon · Accepted Answer

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

Lucky · Answer

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!

RuFdRaFt · Answer

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. ;)

BobberKnob · Answer

innerjoin on mobilehome IDs

Trending News

How do I join multiple tables in a mysql query?

4 Answers