SQL Left Join 2

[johnisotank]

Hi, could someone point out where I am going wrong here please.

I am trying to join two tables (tblJobs and tblNotes) I want to pull in 'JobNo' field from tblJobs and 'Notes' field from tblNotes only if the tblNotes.Type='I'. They join like this..

tblJobs.Job_UniqueKey=tblNotes.UniqueRef

I have tried what I thought was a simple left join but if there is no corresponding Notes (where type is I) then it just doesn't show the JobNo from the tblJobs field.

Any ideas?

Thanks
John

 

[gmmastros]

the problem is that a LEFT JOIN can act like an inner join if you add a where clause condition involving the right table. Got it? Hmmm.....

[tt][blue]
Select *
From tblJobs
Left Join tblNotes
On tblJobs.Job_UniqueKey = tblNotes.UniqueRef
Where tblNotes.Type = 'I'
[/blue][/tt]

This query pulls all rows from the jobs table, and corresponding rows from the Notes table. If there is no corresponding row for a job, all of the columns from the notes table will have null values. Then, you filter on type = 'I'. For job rows without corresponding notes rows, the type will be NULL (not I), so the row is filtered out.

To fix this, move the where clause condition for the notes table in to the ON clause, like this:

Select * 
From   tblJobs
       Left Join tblNotes
         On tblJobs.Job_UniqueKey = tblNotes.UniqueRef
         [!]And tblNotes.Type = 'I'[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[johnisotank]

Brilliant! Works great now.

I have been plugging away at that for almost a day and a half!

Even if your answer wasn't correct, I am still impressed at how fast you wrote that explanation.

Thanks very much
John

 

[gmmastros]

I type fast.

I should probably blog about this.

My blogs

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Yes, blog about it - it's one of very common problem / mistakes I see.

Took me at least 2 cases myself before I learned it by heart.

 

[JerryKlmns]

gmmastros

Dear tek-tiper.

My version till now was

Select *
From   tblJobs Left Join 
             (             
             select tblNotes.UniqueRef 
             from  tblNotes
             where tblNotes.Type = 'I'
             ) As X
         On tblJobs.Job_UniqueKey = X.UniqueRef

But I 'll switch to your approach and "mount" a pinky to you!!!!!

From an ex-MSAccess guy moving to SQL2005, that's not gonna let me sleep tonight!!!!!!