Ambigous error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ambigous error

Author

Topic

jdm95lude
Starting Member

1 Post

Posted - 2008-10-22 : 21:03:09

I'm new to SQL and I'm currently takeing a course over it and I'm having problems with this question. I'm getting the error saying InvoiceID is ambigous

3. Write a SELECT statement that returns four columns: VendorName, InvoiceID, InvoiceSequence, and InvoiceLineItemAmount for each invoice that
has more than one line item in the InvoiceLineItems table.


SELECT VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM	Vendors 
		JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
		Join InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
WHERE InvoiceID =
	(SELECT InvoiceID
	FROM InvoiceLineItems
	WHERE InvoiceSequence > 1)
HAVING COUNT(*) > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:11:07

quote:
Originally posted by jdm95lude

I'm new to SQL and I'm currently takeing a course over it and I'm having problems with this question. I'm getting the error saying InvoiceID is ambigous

3. Write a SELECT statement that returns four columns: VendorName, InvoiceID, InvoiceSequence, and InvoiceLineItemAmount for each invoice that
has more than one line item in the InvoiceLineItems table.

SELECT VendorName, InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM Vendors
JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
Join InvoiceLineItems ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID
WHERE InvoiceID =
(SELECT InvoiceID
FROM InvoiceLineItems
WHERE InvoiceSequence > 1)
HAVING COUNT(*) > 1

this is becuse InvoiceId is present in more than one table so query engine doesnt know which tables field you're refering too. so to avoid error fully qualify it. ie. use tablename.InvoiceID instead of simply invoiceID (it will be like Invoices.InvoiceID,InvoiceLineItems.InvoiceID,...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources