-3

For my homework, I have to enter example queries from the book to sql. There are also example database I downloaded for the homework and properly put them into SQL. The following is what I have to put in but an error message comes up.

SELECT V_CODE, V_NAME, V_STATE, P_CODE, P_DESCRIPT, P_PRICE * P_QOH AS TOTAL
FROM PRODUCT P JOIN VENDOR V ON P.V_CODE = V.V_CODE
WHERE V_STATE IN ('TN','KY')
ORDER BY V_STATE, TOTAL DESC;

--Total value of products from Tennessee and Kentucky

Ambiguous column name 'V_CODE'

Not sure how to fix.

I had to put in an example query from the homework but an error message comes up.

"Ambiguous column name 'V_CODE'"

Stu
  • 30,392
  • 6
  • 14
  • 33
TXC517
  • 1

1 Answers1

0

You need to choose which table you are choosing V_CODE from. In this example it's taken from the PRODUCT table.

SELECT P.V_CODE, V_NAME, V_STATE, P_CODE, P_DESCRIPT, P_PRICE * P_QOH AS TOTAL
FROM PRODUCT P JOIN VENDOR V ON P.V_CODE = V.V_CODE
WHERE V_STATE IN ('TN','KY')
ORDER BY V_STATE, TOTAL DESC;
--Total value of products from Tennessee and Kentucky
Dimi
  • 452
  • 1
  • 9
  • I thought it was already taking from the product table, is the rest of the "FROM" line an issue here? What would cause this? – TXC517 Dec 04 '22 at 21:32
  • You are joining two tables here, it's always *good practice* to qualify your selected columns with the table alias, that way it's obvious when reading the query from which table each column originates. When a column name is only in a single table, SQL Server doesn't require the alias specifically, internally it expands the column names to include the table name - however clearly `V_CODE` column is in both tables for you to able able to join on it - so which column of the two it can choose from should SQL Server use? It may be obvious to you or I that it doesn't actually matter... – Stu Dec 04 '22 at 21:41
  • ... but SQL Server still requires you to be precise. – Stu Dec 04 '22 at 21:42