left join with a where clause - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

left join with a where clause

Author

Topic

plevine
Starting Member

2 Posts

Posted - 2009-02-23 : 15:38:16

Hi,

Table Tasks
This tabkle defines all the possible tasks

ID||||Name
1|||||This
2|||||That
3|||||The Other
4|||||Something else
5|||||Another

Table TaskHistory
This table records only the active tasks associated with a job and the user completed.

Job|||||Update|||||TaskID||||User||||Complete
100|||||2/4/2009||||3||||||||bob|||||null
100|||||2/4/2009||||4||||||||bob|||||null
101|||||2/5/2009||||1||||||||joe|||||2/9/2009

I am trying to write a query that would return all the tasks but would also indicate which specific tasks are associated with a transaction. For instance for transaction 101 I would like

Task|||||||TaskID|||||||complete
1 |||||||1 |||||||2/9/2009
2 |||||||null |||||||null
3 |||||||null |||||||null
4 |||||||null |||||||null
5 |||||||null |||||||null

Is this possible? The problem I have is when I add a WHERE statement to the left join. Then I no longer get a list of all the possible tasks.

(I am doing it this way because the results are being bound to ASP NET datagrid)

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-23 : 15:42:15

LEFT JOIN Table2 AS t2 ON t2.Col = t1.Col AND t2.SomeCol = 'P'

E 12°55'05.63"
N 56°04'39.26"