Re: Outer join in Access


Where TextForm = 'MainForm'
And TextLanguage = 44

Now I would like to outer join and additionally get all Swedish text if they
exist, otherwice just a blank text

Select Eng.TextComponent, Eng.Text, Swe.Text
From Texts Eng
Left Join Texts Swe On Swe.TextForm = Eng.TextForm And Swe.TextComponent =
Eng.TextComponent
Where Eng.TextForm = 'MainForm'
And Swe.TextForm = 'MainForm'
And Eng.TextLanguage = 44
And Swe.TextLanguage = 46

The problem here is I don't get an Outer join! I only get the rows returned
where I also have an Swedish text! (only one row).
Also tried "Left outer join" with same result.
Is there a problem in Access to join with the same table?

Outer joins don't work when you have selection criteria on the outer table

Try

Select Eng.TextComponent, Eng.Text, Swe.Text
From Texts Eng
Left Join Texts Swe On Swe.TextForm = Eng.TextForm
And Swe.TextComponent = Eng.TextComponent
And Swe.TextLanguage = 46
Where Eng.TextForm = 'MainForm'
And Eng.TextLanguage = 44

--
Brian Bushay (TeamB)
Bbushay@xxxxxxxxx
.