SQL join fucntion (3 table)

[delphinewbie]

The following is the corresponded table :

Customer
----------------
CustomerID
CustName
ProductID
SupplierID


Product
----------------
ProductID
ProductName
ProductDesc


Supplier
----------------
SupplierID
SupplierName
SupplierTelNo


I'm try to join all the 3 tables(Join Customer table with Product Table, and Customer Tabel with Supplier Table)
So what SQL statement should i used ?

I guess it should b something like this:

(Let's assume Customer Table is A, Product Table is B, and Supplier Table is C
So A.ProductID=B.ProductID, and A.SupplierID=C.SupplierID ....

I know how to join the table when it's with 2 tables. But how to join all 3 of the table??
Thx in advance for ur help..

 

[Griffyn]

You don't try to join all 3 tables together (ie s A=B, A=C, B=C).

You need to determine which table is the detail table, and then join the other master tables on to it. From here, it sort of looks as if your customer table is the detail table for some reason. Not quite sure what ProductID is doing in there though.

So your example join is correct. JOIN syntax differs depending on your brand of SQL. You would want to use inner joins, which are accomplished by something like this:

SELECT * FROM Customer, Product, Supplier
WHERE Customer.ProductID = Product.ProductID AND Customer.SupplierID = Supplier.SupplierID

That will give you all fields and a single record where the appropriate joins can be made.