Left Outer Join Help 1

[Auguy]

I am trying to get results that have all of the values from the Groups table and any matching values from the CitDetail table. The first procedure skips the Groups entries if there are no CitDetal entries. Is this because of the Inner Join on the third table makes the whole procedure an Inner Join? I can get the results I want using the second procedure listed below. Is there a better way to do this? The code has been simplified to make it easier to read.

Select G.RptCitation, Count(D.Section)
FROM Groups G
LEFT OUTER JOIN CitDetail D On G.OurClass = D.Section And D.CitNo Like 'SC%' And D.IssueDt >= '20120520'
INNER JOIN CrossRef as CR On D.IncdNo = CR.IncdNo
Group by G.Citation
Order by G.Citation

Select G.Citation, Count(S.Section)
FROM Groups G
LEFT OUTER JOIN (
	Select D.Section
	FROM CitDetail D
	INNER JOIN CrossRef as CR On D.IncdNo = CR.IncdNo
	Where D.CitNo Like 'SC%' And D.IssueDt >= '20120101') as S
On G.OurClass = S.Section
Group by G.Citation
Order by G.Citation

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Yes.

Try making both joins a LEFT join.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

I can do that but the third table is used to filter out some unwanted records fronm the second table. So I think I need the Inner Join there, or am I missing something?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

You can put the filter condition in the join clause.


Ex:
[tt]
Left Join ThirdTable
On SecondTable.IdColumn = ThirdTable.IdColumn
And ThirdTable.StringColumn Like 'SC%'[/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Ah, of course, so simple. Thanks George

Auguy
Sylvania/Toledo Ohio