multi-inner join statement

Question

I'm currently trying to draw multiple tables through several inner-join statements. However, I am continually hit with the following error:

ORA-00904: "PART"."ITEM_CLASS": invalid identifier

I've visited several other pages here but that problem did not coincide with mine.

Here is the SQL query:

SELECT Slsrep_Number, AVG(Commission_Rate) AS AVG_Rate, MAX(Total_Commission) as MAX_Comission 
  FROM Sales_Rep 
 WHERE Sales_Rep.Slsrep_Number = Customer.Slrsrep_Number 
   AND Customer.C_Number = Orders.C_Number 
   AND Orders.Order_Number = Order_Line.Order_Number 
   AND Order_Line.Part_Number = Part.Part_Number 
   AND Part.Item_Class = 'SG';

Here are the tables being used (screenshots):

The pictures are listed in the order that I am referencing them in the SQL query.

Where's your from clause entry for `Customer` and `Part` tables? — Kaushik Nayak, Apr 11 '19 at 03:36

score 2 · Accepted Answer · answered Apr 11 '19 at 11:05

You need to reference the tables in the FROM clause. You can't just reference them.

You should also use table aliases and proper, explicit, standard JOIN syntax.

So:

SELECT sr.Slsrep_Number,
       AVG(?.Commission_Rate) AS AVG_Rate, 
       MAX(?Total_Commission) as MAX_Comission 
FROM Sales_Rep sr JOIN
     Customer c
     ON sr.Slsrep_Number = c.Slrsrep_Number JOIN
     Orders o
     ON c.C_Number = o.C_Number JOIN
     Order_Line ol
     ON o.Order_Number = ol.Order_Number 
     Part p
     ON ol.Part_Number = p.Part_Number 
WHERE p.Item_Class = 'SG';

The ? is for the alias for the column with the commission.

The query now "looks" right. However, I don't think it is particularly useful. If that is the case, ask another question and provide sample data, desired results, and an explanation of what you want to accomplish.

score 0 · Answer 2 · answered Apr 11 '19 at 03:42

Customer,Orders,Order_Line and Part Tables are missing in the from clause.Try This.

SELECT Slsrep_Number,
       AVG(Commission_Rate) AS AVG_Rate,
       MAX(Total_Commission) as MAX_Comission
  FROM Sales_Rep,Customer,Orders,Order_Line,Part
 WHERE Sales_Rep.Slsrep_Number = Customer.Slrsrep_Number
   AND Customer.C_Number = Orders.C_Number
   AND Orders.Order_Number = Order_Line.Order_Number
   AND Order_Line.Part_Number = Part.Part_Number
   AND Part.Item_Class = 'SG';

multi-inner join statement

2 Answers2