0

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.

  • 3
    I believe your first query should be: 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 the HAVING clause is not necessary. – Lennart May 3 '16 at 18:25
  • But MSSQL allows the use of having only if a 'Group By' is used. I tried to keep the query as short as possible. – MSIS May 3 '16 at 18:35
  • Assuming 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 an HAVING clause if you want to select vendors with more than n invoices, or whose invoice value exceeds a certain amount etc. – Lennart May 3 '16 at 19:01
  • Good point, I was only thinking about n=1, where that would not matter. – MSIS May 3 '16 at 19:06
  • 1
    I am unclear why we have moved on to a Stored Proc that returns vendors with more than N invoices if the requirement was "...a T-SQL statement to find all vendors with at least one invoice." – Chad Mattox May 3 '16 at 19:13
2

You don't need the WHERE clause because you are doing an inner join, which ensures you will only get vendors that have an invoice:

SELECT DISTINCT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID

OR

SELECT DISTINCT VendorName 
FROM Vendors V 
WHERE EXISTS (SELECT 1 FROM Invoices I WHERE V.VendorId=I.VendorID)
2

what do you expect count to count?
you need to group by
and use having rather than where

SELECT VendorName 
FROM Vendors V 
join Invoices I on V.VendorId = I.VendorID 
group by VendorName 
having Count(*) >= @Number 
  • But how do I return a value? EDIT Shouldn't this be a procedure that allows me to get a result for different values of @NumberofInvoices? Sorry, I am a beginner only. – MSIS May 3 '16 at 18:36
  • But it does return a value - VendorName. Exactly what you have in the current procedure. What do you need? – paparazzo May 3 '16 at 18:37
  • I meant , can I get a procedure that will give me a result for different values of @Number of Invoices? Basically, if I wanted to know for, say, n=3 or n=4 . How would I get the VendorNames? – MSIS May 3 '16 at 18:39
  • What makes you think this answer does not do that? Have you tried it? – paparazzo May 3 '16 at 18:40
  • 1
    DECLARE requires a type. If you want 3, try DECLARE @NumberofInvoices INT = 3 which will work if you are on SQL Server 2008 or higher. For earlier versions, DECLARE @NumberofInvoices INT SET @NumberofInvoices = 3 – Arthur D May 3 '16 at 21:31
1

If a question (in a Microsoft SQL Server specific context) asks for "a T-SQL statement to do X", then they are just asking for any SQL statement that would be valid on a SQL Server instance.

It does not have to use syntax that only works on SQL Server (but not MySQL etc). Standard ANSI SQL is fine if it works on SQL Server. (Not all ANSI SQL is valid on all SQL Server versions.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.