Syntax for nested joins on 3 tables

  • Thread starter Thread starter Michael_Randall
  • Start date Start date
M

Michael_Randall

Syntax for Nested Joins

I have a database with 4 tables and I am trying to query information from 3
of the tables. I would ideally like to obtain the information by using nested
joins instead of 2 separate queries.
Tables/Structures:
CUSTOMER(CustomerPK, Name, Phone, EmailAddress)
APPLIANCE(SerialNumber, Type, Version, DateofCreation)
APPLIANCE_REPAIR(InvoiceNumber, SerialNumber, DateRepair, Description, Cost,
CustomerPK)

I would like to get a list of all repairs in the APPLIANCE_REPAIR table,
specifically, InvoiceNumber, SerialNumber, and Description. From the
APPLIANCE table I would like to get the corresponding Type of appliance. And,
from the CUSTOMER table I would like the corresponding customer Name and
Phone number.

Current multi-query solution:

First Query
SELECT S.Type, SR.RepairInvoiceNumber, SR.SerialNumber, SR.Date,
SR.Description, SR.Cost, SR.CustomerSK
FROM STOVE AS S RIGHT JOIN STOVE_REPAIR AS SR ON
S.SerialNumber=SR.SerialNumber;

Second Query
SELECT RepairDetailsType.*, CUSTOMER.Name, CUSTOMER.Phone
FROM RepairDetailsType INNER JOIN CUSTOMER ON RepairDetailsType.CustomerSK =
CUSTOMER.CustomerSK;

As I asked before, is this possible to achieve by using nested joins all in
one query? I've tried to solve this myself but was unable to.
 
Sorry, I need to modify my examples so that they reflect the correct fields
in the tables:

Tables/Structures:
CUSTOMER(CustomerPK, Name, Phone, EmailAddress)
APPLIANCE(SerialNumber, Type, Version, DateofCreation)
APPLIANCE_REPAIR(InvoiceNumber, SerialNumber, DateRepair, Description, Cost,
CustomerPK)
Click to expand...

First Query
SELECT A.Type, AR.InvoiceNumber, AR.SerialNumber, AR.DateRepair,
AR.Description, AR.Cost, AR.CustomerPK
FROM APPLIANCE AS A RIGHT JOIN APPLIANCE_REPAIR AS AR ON
A.SerialNumber=AR.SerialNumber;

Second Query
Click to expand...
SELECT RepairDetailsType.*, CUSTOMER.Name, CUSTOMER.Phone
FROM RepairDetailsType INNER JOIN CUSTOMER ON RepairDetailsType.CustomerPK =
CUSTOMER.CustomerPK;
Click to expand...

As I asked before, is this possible to achieve by using nested joins all in
 
Not as specific as you requested with regard to field outputs, but this is
the general structure of a single query:

SELECT APPLICANCE_REPAIR.*, APPLICANCE.*,
CUSTOMER.* FROM (APPLICANCE_REPAIR
LEFT JOIN APPLICANCE ON
APPLICANCE_REPAIR.SerialNumber =
APPLIANCE.SerialNumber) LEFT JOIN
CUSTOMER ON APPLIANCE_REPAIR.CustomerPK =
CUSTOMER.CustomerPK;
 
Back
Top