LEFT OUTER JOIN & WHERE clause issue

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)

LEFT OUTER JOIN & WHERE clause issue

Author

Topic

bradbox
Starting Member

2 Posts

Posted - 2008-05-05 : 16:28:40

Hello,

I have two tables:

tasks
id int
userid int
otherstuff int

sample rows:
1,11,2
2,11,2

times
id int
taskid int
startweek datetime
status int

sample rows:
1,1,'04/28/2008',1
2,1,'04/28/2008',1
2,2,'04/28/2008',1

I want to retrieve:
ALL [tasks] with [times].[status] for a specific user for a given week, whether rows exist in [times] or not.

Here's my query:

select tasks.id, times.status
from tasks
LEFT OUTER JOIN times ON tasks.id = times.taskid
where upt.userid=11 AND times.startweek='05/05/2008'

That just doesn't return any the records though for the 05/05/2008 date. Its as though I want to join with a WHERE clause on the [times] table. What can I do?

Thanks in advance,

Brian

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-05 : 16:40:10

see:

http://www.sqlteam.com/article/additional-criteria-in-the-join-clause
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

bradbox
Starting Member

2 Posts

Posted - 2008-05-05 : 17:07:26

Thank you so very, vey much for saving me hours of head ache!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources