CODEWITHSUNDEEP
sql
InvertReality
InvertReality

Reputation: 39

SQL - Specified field could relate to more than one table

I am a beginner to SQL and have been playing around with it. There are two tables. One called Customers and one called Orders. I am doing a count on how many orders are related to the customer ID but it keeps throwing up an error. I have the following code:

SELECT CustomerID, COUNT (orders.OrderID) AS TotalOrders
FROM Customers AS cust, Orders AS orders
WHERE cust.CustomerID = orders.CustomerID

It throws up an error that the CustomerID field could appear in more than one table.

If I change the SELECT CustomerID to ContactName for example, the query will work fine. However, I know the problem is that the CustomerID field is in both the Customers and Orders table. Thanks in advance for any help.

Upvotes: 0

Views: 133

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146603

You have to qualify the field name with the tablename, or an alias... Using Aliases is recommended. Also, recommend you switch to ANSI-92 Join syntax.

  SELECT c.CustomerID, COUNT (o.OrderID) AS TotalOrders
  FROM Customers c Join Orders o 
     On c.CustomerID = o.CustomerID
  GROUP BY c.CustomerID

Upvotes: 1

Niro
Niro

Reputation: 776

Should be:

SELECT cust.CustomerID, COUNT (orders.OrderID) AS TotalOrders
FROM Customers AS cust, Orders AS orders
WHERE cust.CustomerID = orders.CustomerID

Upvotes: -1

Kermit
Kermit

Reputation: 34062

Try this

SELECT cust.CustomerID, COUNT (orders.OrderID) AS TotalOrders
FROM Customers AS cust
JOIN Orders AS orders ON cust.CustomerID = orders.CustomerID
GROUP BY cust.CustomerID

This query uses explicit JOIN syntax that is ANSI standard. This is best practice. The only thing you were missing was the GROUP BY to group the aggregate. You also want to make sure that prefix the table name to the column to prevent ambiguity.

Upvotes: 4

Related Questions