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 |
|
kieran5405
Yak Posting Veteran
96 Posts |
 Posted - 2008-09-15 : 04:58:00
|
| Hi,I have the below SQL statement. I have a Client table with many clients and then a Commets table with comments entered each day 1 per client. I want to select out each client and any comment that has been entered for that day. I feed in todays date to the SQL statement and the rows are returned so all works fine.However i now need to select out the client even if no comment has been entered in the Comments table for that day. I tried a LEFT JOIN but i presume the WHERE clause is excluding those clients who have no comment entered for that day. I cant seem to get this working at all.Is there a better way to write this. Thanks for any help...SELECT Clients.Name, Comments.Comment FROM Clients LEFT JOIN Comments ON clients.ID = Comments.clientID WHERE DAY(Comments.ClosedDate) = (" & day & ") AND Month(Comments.ClosedDate) = (" & month & ") AND Year(Comments.ClosedDate) = (" & year & ") |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 05:00:33
|
quote:
Originally posted by kieran5405
Hi,I have the below SQL statement. I have a Client table with many clients and then a Commets table with comments entered each day 1 per client. I want to select out each client and any comment that has been entered for that day. I feed in todays date to the SQL statement and the rows are returned so all works fine.However i now need to select out the client even if no comment has been entered in the Comments table for that day. I tried a LEFT JOIN but i presume the WHERE clause is excluding those clients who have no comment entered for that day. I cant seem to get this working at all.Is there a better way to write this. Thanks for any help...SELECT Clients.Name, Comments.Comment FROM Clients LEFT JOIN Comments ON clients.ID = Comments.clientID AND DAY(Comments.ClosedDate) = (" & day & ") AND Month(Comments.ClosedDate) = (" & month & ") AND Year(Comments.ClosedDate) = (" & year & ")
Try using AND instead of WHERE |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-15 : 05:25:33
|
[code]SELECT Clients.Name, Comments.Comment FROM Clients LEFT JOIN Comments ON Comments.ClientID = Clients.ID AND Comments.ClosedDate >= '20080815' AND Comments.ClosedDate < '20080816'[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
kieran5405
Yak Posting Veteran
96 Posts |
Posted - 2008-09-15 : 05:34:28
|
| great...that works...thanks...never knew u could extend sql like that!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-15 : 05:36:07
|
| Use Pesos method if you want to make use of index on date column |
|
|
|
|
|
|
Left Join problem