CODEWITHSUNDEEP
sqloracle-databaseselectjoin
B-B.
B-B.

Reputation: 193

SQL - Joining multiple tables?

I'm trying to join multiple tables to create a list of purchase orders with their total cost. I have tried to following SELECT statement which returns an error of "'OA'.'AIRCRAFT_CODE' INVALID IDENTIFIER"

SELECT al.airline_code AS “Airline Code”, 
    po.purchase_order_no AS “Order Number”,
    ac.aircraft_code as “Aircraft Code”,
    oa.aircraft_quantity as “Quantity of Aircraft Ordered”,
    SUM(oa.aircraft_quantity * ac.aircraft_price) AS “Order Total”
FROM Aircraft ac, Airline al, Ordered_Aircraft oa, Purchase_Order po
    JOIN Airline al ON po.airline_code = al.airline_code
    JOIN Aircraft ac ON oa.aircraft_code = ac.aircraft_code
    JOIN Purchase_Order po ON oa.purchase_order_no = po.purchase_order_no
GROUP BY po.purchase_order_no
ORDER BY al.airline_code ASC;

The database structure is as follows:

aircraft

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 AIRCRAFT_CODE                             NOT NULL VARCHAR2(5 CHAR)
 AIRCRAFT_TYPE                             NOT NULL VARCHAR2(30 CHAR)
 AIRCRAFT_PRICE                            NOT NULL NUMBER(11,2)

airline

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 AIRLINE_CODE                              NOT NULL VARCHAR2(4 CHAR)
 AIRLINE_NAME                              NOT NULL VARCHAR2(20 CHAR)
 AIRLINE_ADDRESS                           NOT NULL VARCHAR2(100 CHAR)
 AIRLINE_CITY                              NOT NULL VARCHAR2(20 CHAR)
 AIRLINE_COUNTRY                           NOT NULL VARCHAR2(20 CHAR)

ordered_aircraft;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 PURCHASE_ORDER_NO                         NOT NULL NUMBER(3)
 AIRCRAFT_CODE                             NOT NULL VARCHAR2(5 CHAR)
 AIRCRAFT_QUANTITY                         NOT NULL NUMBER(2)

purchase_order

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 PURCHASE_ORDER_NO                         NOT NULL NUMBER(3)
 AIRLINE_CODE                                       VARCHAR2(4 CHAR)
 PURCHASE_ORDER_DATE                       NOT NULL DATE

Any help would be great. Thank you.

Upvotes: 0

Views: 197

Answers (1)

D Stanley
D Stanley

Reputation: 152644

Take the tables that you are joining out of the list after FROM:

SELECT 
    al.airline_code AS “Airline Code”, 
    po.purchase_order_no AS “Order Number”, 
    ac.aircraft_code as “Aircraft Code”, 
    oa.aircraft_quantity as “Quantity of Aircraft Ordered”, 
    SUM(oa.aircraft_quantity * ac.aircraft_price) AS “Order Total”
FROM Ordered_Aircraft oa   /*  , Airline al, Ordered_Aircraft oa, Purchase_Order po */   <---
JOIN Purchase_Order po
    ON oa.purchase_order_no = po.purchase_order_no
JOIN Airline al 
    ON po.airline_code = al.airline_code
JOIN Aircraft ac
    ON ac.aircraft_code = oa.aircraft_code
GROUP BY po.purchase_order_no
ORDER BY al.airline_code ASC;

Using a comma-separated list of tables is a different JOIN syntax. If you are using JOIN there's no need to reference them again.

Upvotes: 2

Related Questions