Simple Database Question

[geek_till_itMHZ]

Iam writing (or trying to write) a simple join query statement in Query Analyzer. As of right now Im getting the error message
Ambiguous column name 'categoryID'.

My Statement is ;

Select categoryID, Max (StockPrice) as maxStockPrice, MIN(StockPrice) as minStockPrice, AVG(StockPrice) as avgStockPrice
From Category, InventoryPart
Where Category.CategoryID = InventoryPart.CategoryID;

Im thinking it has something to do with the fact that SQL isn't sure if it should pull the column categoryID from the Category Table or InventoryPart Table.

1) Am I correct in thinking the above
2) how do I fix it?

Any Help Would be Greatly Appreciated

[RipperJT]

Hi,

You could prefix the table name to the column e.g.:
SELECT Category.catagoryID, MAX( .....

The query engine doesn't want to guess which table is the one it's supposed to pull that column from...

Jason.

Iam writing (or trying to write) a simple join query statement in Query Analyzer. As of right now Im getting the error message
Ambiguous column name 'categoryID'.

My Statement is ;

Select categoryID, Max (StockPrice) as maxStockPrice, MIN(StockPrice) as minStockPrice, AVG(StockPrice) as avgStockPrice
From Category, InventoryPart
Where Category.CategoryID = InventoryPart.CategoryID;

Im thinking it has something to do with the fact that SQL isn't sure if it should pull the column categoryID from the Category Table or InventoryPart Table.

1) Am I correct in thinking the above
2) how do I fix it?

Any Help Would be Greatly Appreciated

[geek_till_itMHZ]

Hi,

You could prefix the table name to the column e.g.:
SELECT Category.catagoryID, MAX( .....

The query engine doesn't want to guess which table is the one it's supposed to pull that column from...

Jason.

Thats What I did but then I got an error saying
Category.categoryID is and invalid column because it is not used in an aggregate function or the GroupBY Clause...so I put it in the GroupBY Clause. Problem solved...maybe

[_taz_]

Thats What I did but then I got an error saying
Category.categoryID is and invalid column because it is not used in an aggregate function or the GroupBY Clause...so I put it in the GroupBY Clause. Problem solved...maybe

that's because the analyzer stopped when it hit your first error. if you had the Category.CategoryID in there to start with then you would have gotten this error right away. When you use one of the Aggregate Functions like Min,Max,AVG,Sum, etc.. you have to have a group by clause

[geek_till_itMHZ]

Iam writing (or trying to write) a simple join query statement in Query Analyzer. As of right now Im getting the error message
Ambiguous column name 'categoryID'.

My Statement is ;

Select categoryID, Max (StockPrice) as maxStockPrice, MIN(StockPrice) as minStockPrice, AVG(StockPrice) as avgStockPrice
From Category, InventoryPart
Where Category.CategoryID = InventoryPart.CategoryID;

Im thinking it has something to do with the fact that SQL isn't sure if it should pull the column categoryID from the Category Table or InventoryPart Table.

1) Am I correct in thinking the above
2) how do I fix it?

Any Help Would be Greatly Appreciated

Youre exactly right you need to specify which table you wnat to pull category ID from by putting either Category.categoryID or InventoryPart.categoryID so It would look like :

Select Category.categoryID, Max (StockPrice) as maxStockPrice, MIN(StockPrice) as minStockPrice, AVG(StockPrice) as avgStockPrice
From Category, InventoryPart
Where Category.CategoryID = InventoryPart.CategoryID;

Also watch your caps in your where clause Category.categoryID

And the easiest way to fix the GROUPBY clause issue is to make a GROUPBY clause and add Category.categoryID

[mail2saion]

You can try with below technique:

If you use Sql Server Management studio then:

open.
Connect to the remote server.Right click on the server instance.Select properties.
Click Connections.Check to allow remote connections to this server.

[princekool]

Iam writing (or trying to write) a simple join query statement in Query Analyzer. As of right now Im getting the error message
Ambiguous column name 'categoryID'.

My Statement is ;

Select categoryID, Max (StockPrice) as maxStockPrice, MIN(StockPrice) as minStockPrice, AVG(StockPrice) as avgStockPrice
From Category, InventoryPart
Where Category.CategoryID = InventoryPart.CategoryID;

Im thinking it has something to do with the fact that SQL isn't sure if it should pull the column categoryID from the Category Table or InventoryPart Table.

1) Am I correct in thinking the above
2) how do I fix it?

Any Help Would be Greatly Appreciated

My thoughts are telling me you want to join two tables with related values. The tables are Category & InventoryPart, am i right?
Could you state what results are you expecting from the query?
And Is Category.CategoryID similar to InventoryPart.CategoryID? What is the difference between the two columns from the two tables?