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.
| Author |
Topic |
|
tjoppie
Starting Member
4 Posts |
 Posted - 2010-09-15 : 15:10:07
|
| Hi there,I've got two tables:tClient:ClientID Name1 John2 Sally3 Joe4 NicktInvoice:InvoiceID ClientID DateAdded1 1 2010/09/012 1 2010/09/023 3 2010/09/01I'm trying to write sproc where I need the following info:All the invoices for all clients between 2010/09/01 and 2010/09/30, but the catch is, I need all the clients to be in the dataset that get's returned... so for eg my return dataset will look like this:ClientID InvoiceID DateAdded1 1 2010/09/011 2 2010/09/022 NULL NULL3 3 2010/09/014 NULL NULLNow I've tried something like this:SELECT tClient.ClientID, tInvoice.InvoiceID, tInvoice.DateAddedFROM tClient LEFT OUTER JOIN tInvoice ON tClient.ClientID = tInvoice.ClientIDWHERE (tInvoice.DateAdded BETWEEN CONVERT(DATETIME, '2010-09-01 00:00:00', 102) AND CONVERT(DATETIME, '2010-09-30 00:00:00', 102))but this returns only return the clients who has invoices for that date range. I need all the clients returned from tClient in my dataset, as shown above. Any idea on how I'll be able to do this?Thanks alot in advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
t-sql query return all rows from table1