Need help with a SELECT statement

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)

Need help with a SELECT statement

Author

Topic

rod4president
Starting Member

3 Posts

Posted - 2007-08-16 : 17:18:26

My ultimate goal is to return customers that have 1 or more quotes but have never had any of them invoiced... Here's my structure.

Customers
-------------
CustomerID
CustomerName

Quotes
-------------
QuoteID
CustomerID

Invoices
-------------
InvoiceID

CustomerID is used to join Quotes and Customers, and the QuoteID and InvoiceID join those tables. I want to select all customers that do not have an InvoiceID. Any help would be greatly appreciated, I'm totally drawing a blank here. Thanks.

Rod

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 17:21:12

Use LEFT JOIN

SELECT c.CustomerID
FROM Customers AS c
LEFT JOIN Quotes AS q ON q.CustomerID = c.CustomerID
LEFT JOIN Invoices AS i ON i.InvoiceID = q.QuoteID
WHERE i.InvoiceID IS NULL

E 12°55'05.25"
N 56°04'39.16"

rod4president
Starting Member

3 Posts

Posted - 2007-08-16 : 17:46:12

Peso- Thanks for the quick response. That statement gets me most of the way there, however, customers that have multiple QuoteIDs and only some of those QuoteIDs have corresponding InvoiceIDs still show up. I'm looking to SELECT customers that have never had an InvoiceID even if they have had multiple QuoteIDs. Does that make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 17:47:38

SELECT c.CustomerID
FROM Customers AS c
LEFT JOIN Quotes AS q ON q.CustomerID = c.CustomerID
LEFT JOIN Invoices AS i ON i.InvoiceID = q.QuoteID
GROUP BY c.CustomerID
HAVING MAX(i.InvoiceID) IS NULL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 17:48:22

Or simpler

SELECT c.CustomerID
FROM Customers AS c
LEFT JOIN Quotes AS q ON q.CustomerID = c.CustomerID
WHERE q.InvoiceID IS NULL

E 12°55'05.25"
N 56°04'39.16"

rod4president
Starting Member

3 Posts

Posted - 2007-08-16 : 17:50:20

YES! Thank you so much! I was pulling my hair out for hours!

Rod

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources