CODEWITHSUNDEEP
mysqlsql
Thomas Tran
Thomas Tran

Reputation: 23

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

Upvotes: 2

Views: 811

Answers (3)

Alex_L
Alex_L

Reputation: 2654

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

Upvotes: 1

xkeshav
xkeshav

Reputation: 54050

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

Upvotes: 0

Romain
Romain

Reputation: 12819

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)

Upvotes: 2

Related Questions