How to create SQL subquery ON JOIN using multiple tables

Question

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, customer 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;

Answer 1

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)

Answer 2

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

Answer 3

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