Selecting information from 3 or more tables with a common id. 1

[shaunieb]

Hi I have 3 or more tables i need to get info from. They are all related by unit_id, but im not sure how to do it. Can anyone give me some ideas?

The tables are displayed below.
Thank you in advance.

BOND
unit_id
bank
accountno
banktel
contactname
contacttel
Project_Id

BUYER
Id
Project_Id
Firstname
Lastname
Cell
Landline
Fax
EMail
PostalAddress
ResidentialAddress
PartnerName
Comments
Unit_Id

contract
id
contractdate
suspendive
firmed
lodged
rrreg
purchaseprice
occdate
stnumber
dateregistered
SuspensiveRem
OccDateRem
ProjectID
Unit_id

 

[TonyGroves]

It obviously depends on what you are trying to do, but as an example, you can retrieve various information related to the unit_id from all 3 tables as follows:
[tt]
SELECT
unit_id,
bond.bank,bond.accountno,
buyer.id,buyer.firstname,buyer.lastname,
contract.id,contract.contractdate
FROM
bond
JOIN buyer USING (unit_id)
JOIN contract USING (unit_id)
WHERE unit_id BETWEEN 123 AND 456
[/tt]

 

[TonyGroves]

Sorry, the first "unit_id" in that query is ambiguous, so you can it with "bond.unit_id".

 

[shaunieb]

thanks tony! working like a charm