Tablename could refer to more than one table listed in the FROM clause.

[Wallatrix]

Hi,

I am taking a database concepts class and I cannot, for the life of me, figure out how to effectively code SQL queries. Every time I run a query I get some sort of error message. I've read other forums on this site about how to fix this issue that I am having, but nothing seems to work. Yes, I have qualified my fields and yes I have every table containing the specific tablename listed... but nothing seems to work. Here is my source code, I hope someone can help me.

SELECT Customer.CustomerName, Orders.OrderNum, OrderLine.OrderNum, SUM(NumOrdered * QuotedPrice)
         FROM Customer, OrderLine, Orders
         WHERE Customer.CustomerNum = Orders.CustomerNum
         AND Orders.OrderNum = OrderLine.OrderNum
         GROUP By CustomerName, OrderNum

When I try to run this query I get an error message that states "The specified field 'OrderNum' could refer to more than one table listed in the FROM clause of your SQL statement." Even though I have qualified my fields and there are only 2 tables (Orders and OrderLine) that has a OrderNum column. Please help me!

[JoeM]

You have listed the OrderNum field twice in your select statement (one from each table), and you haven't qualified this field at all in your "GROUP BY" statement.
Since you are only returning records where the OrderNum between the Orders and OrderLine tables are the same, there is no need to return it twice (once from each field) in your Select statement.

It is also better form to define your relationships in your FROM clause than to do it in the WHERE clause, i.e.

SELECT Customer.CustomerName, Orders.OrderNum, SUM(NumOrdered * QuotedPrice)
         FROM Customer, 
         INNER JOIN Orders
         ON Customer.CustomerNum = Orders.CustomerNum
         INNER JOIN OrderLine
         ON Orders.OrderNum = OrderLine.OrderNum
         GROUP By Customer.CustomerName, Orders.OrderNum

[orange]

Since OrderNum is a field in both Orders and Orderline tables, you must include the table reference in your Group BY statement to explicitly tell Access/Jet what you mean. (Also called disambiguate)


GROUP By CustomerName, Orders.OrderNum
Or
GROUP By CustomerName, Orderline.OrderNum


OOps: I see JoeM has responded while I was fighting with a bad internet connection.

[Wallatrix]

You have listed the OrderNum field twice in your select statement (one from each table), and you haven't qualified this field at all in your "GROUP BY" statement.
Since you are only returning records where the OrderNum between the Orders and OrderLine tables are the same, there is no need to return it twice (once from each field) in your Select statement.

It is also better form to define your relationships in your FROM clause than to do it in the WHERE clause, i.e.

SELECT Customer.CustomerName, Orders.OrderNum, SUM(NumOrdered * QuotedPrice)
         FROM Customer, 
         INNER JOIN Orders
         ON Customer.CustomerNum = Orders.CustomerNum
         INNER JOIN OrderLine
         ON Orders.OrderNum = OrderLine.OrderNum
         GROUP By Customer.CustomerName, Orders.OrderNum

Thank you for helping me out. I didn't think of that solution; too much cobol and java in my head. My teacher wants us to start out using the WHERE clause for joining tables until further on in the class.

[JoeM]

My teacher wants us to start out using the WHERE clause for joining tables until further on in the class.

I think I just lost a lot of respect for your teacher!