How to create SQL subquery ON JOIN using multiple tables

I have the folowing tables.

ORDER

OrderNumber

CustomerNumber

EmployeeNumber

OrderDate

CUSTOMER

CustomerNumber

Name

Address

EMPLOYEE

EmployeeNumber

Name

Address

ORDERDETAIL

OrderNumber

Qty

Description

Price

Let say ORDERDETAIL table has 10 records

I would like to write a query that will return 10 records from ORDERDETAIL table to include Employee name, employee address, cust开发者_如何学Goomer name, customer address and and order Date.

I know that I could write a query and use INNER JOIN to get the info from ORDER table, but how do you create the rest of query to get the info from the CUSTOMER and EMPLOYEE tables.

SELECT * 
  FROM OrderDetail D 
       INNER JOIN Order O 
          ON D.OrderNumber = O.OrderNumber;

Just add some more joins...

SELECT *
FROM OrderDetail D
JOIN Order USING (OrderNumber)
JOIN Customer USING (CustomerNumber)
JOIN Employee USING (EmployeeNumber)

You might want to re-order the JOINs in order to have the smallest tables first, as this could provide you with some performance boost (depending on your server's version, the most recent will optimize the join for you and might actually execute the joins in the "probably best" way).

Also, in the MySQL dialect at least, JOIN implicitly expands to INNER JOIN, and writing

A JOIN B USING (COL)

is equivalent to writing

A JOIN B ON (A.COL = B.COL)

SELECT * 
FROM OrderDetail D 
INNER JOIN Order O ON D.OrderNumber = O.OrderNumber
INNER JOIN Eployee E on O.EployeeNumber = E.EployeeNumber
INNER JOIN Customer C on O.CustomerNumber = C.CustomerNumber

if you have foreign key reference between

ORDER.EmployeeNumber and EMPLOYEE.EmployeeNumber
ORDER.CustomerNumber and CUSTOMER.CustomerNumber

then try this

SELECT 
      E.name AS employeeName, 
      E.Address AS employeeAddress,
      C.name AS customerName,
      C.Address AS customerAddress.
      O.OrderDate
FROM OrderDetail D 
INNER JOIN Order O ON D.OrderNumber = O.OrderNumber
INNER JOIN EMPLOYEE E ON E.EmployeeNumber = 0.EmployeeNumber
INNER JOIN CUSTOMER C ON C.CustomerNumber= 0.CustomerNumber 
LIMIT 0,10