We are given tables Vendors(VendorId, VendorName, ..) , Invoices(InvoiceId, VendorId...) I am going over a book exercise that asks for a T-SQL statement to find all vendors with at least one invoice. But I am confused, since this can be perfectly done with standard SQL:
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
GROUP BY VendorName having Count(InvoiceId) > = 1
So, what could they be asking? Should I come up with a procedure to select all vendors with more than n invoices; n= 0,1,2 ?
If so, why is this not working for the general case of n invoices:
CREATE Procedure InvoiceCount @NumberofInvoices Int
AS
BEGIN
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID
WHERE Count(InvoiceId) > = @Number of Invoices
EXEC InvoiceCount @Number of Invoices = n
Why doesn't this work? Thank you.
SELECT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID GROUP BY VendorName HAVING Count(InvoiceId) > = 1
. But since the JOIN itself guarantees that there exists at least one invoice theHAVING
clause is not necessary.WHERE Count(InvoiceId) > = 1
works (which I don't think it should), you would still get duplicate VendorNames for those that have more than one invoice. So you need to either use distinct, or group by to get distinct VendorNames. Group by has the advantage that you can easily add anHAVING
clause if you want to select vendors with more than n invoices, or whose invoice value exceeds a certain amount etc.