Ambiguous column name error : SQL count, join, group by

Question

I have two tables as below, one table has number of available units (stock), I'm trying to return the stock count of each product category, and joining it with secondary table to view the description and price etc.

When I run the below query, I get "Ambiguous column name 'productID'."

What am I doing wrong?

SQL Query:

select productID, count (stock)as available_count
from product_units
join  product_type ON product_type.description = product_units.productID
group by productID  

This returns an error:

Ambiguous column name 'productID'.

Table product_type

productID  description  price 
101        tent         20.00
102        xltent       50.00

Table product_units

unitID  productID stock
1       101       1
2       101       1
3       101       1
4       102       1

---

Orginal SQL query to get stock count, which works:

select productID, count (stock)as available_count
from  product_units
group by productID

I'm using SQL Server 2008 R2 with Coldfusion

Answer 1

I think your error is more likely "Ambiguous column name 'productID'." And, I'm guessing the join should be on that field as well:

select product_units.productID, count (stock)as available_count
from product_units
join  product_type ON product_type.productID = product_units.productID
group by product_units.productID 

To select all rows from the product_type table use a right outer join:

select product_units.productID, count (stock)as available_count
from product_units
right outer join  product_type ON product_type.productID = product_units.productID
group by product_units.productID 

To select all information from the product type table, do the aggregation first and then join:

select pt.*, pu.available_count
from (select productId, count(stock) as available_count
      from product_units
      group by productId
     ) pu join
     product_type pt
     on pt.productID = pu.productId;