PUtting an AND in a RIGHT OUTER JOIN

[darbid]

Hi guys,

I do not suppose anyone has a workaround for this below. As far as I understand Jet will not do the red AND in the join. Could anyone give me some advice on how to fix this.

SELECT * 
FROM tbl_Licence_Potential 
RIGHT OUTER JOIN [tbl_LuT Vorgang] 
ON tbl_Licence_Potential.[LuT Aktenzeichen] = [tbl_LuT Vorgang].[LuT Aktenzeichen]
[COLOR=Red]AND tbl_Licence_Potential.GJ = '08/09' [/COLOR]
WHERE [tbl_LuT Vorgang].Reporting=True 
AND [tbl_LuT Vorgang].[Bearbeiter LuT] = 'BOI';

Thanks in advance.

 

[honda882000]

You mean something like an outer condition? You can try creating a new field as: IIF(tbl_Licence_Potential.GJ = "08/09","Y",Null). Then, for whatever you are using this query for, the records that are applicable from tbl_Licence_Potential will have a 'Y' for this column. Or, you could also create a new query which filters the records from tbl_Licence_Potential, with you condition, and then outer join to that. Would this work for you?

 

[darbid]

Hi Honda,

I got an email about your original post and then could not find it .

Regarding your second post.


After doing more research I gave up on DAO and recoded it to be a ADODB and use SQL server. In SQL server I can use this posted sql.

Thanks for helping.

 

[honda882000]

I deleted the first post after I realized you meant a outer condition.

Thats good to hear. Access is a great tool, but I really hate Jet . I am used to Oracle servers myself, which you can simply type:
condition_item(+) = condition.

Have a great weekend!

 

[LPurvis]

Jet does support both multiple conditions on a join using fields and literal values.
It will accept field join conditions impicitly.
Literal values such as you were attempting to use however need a bit of qualification to be parsed correctly.
i.e.

SELECT *
FROM tbl_Licence_Potential
RIGHT OUTER JOIN [tbl_LuT Vorgang]
ON (tbl_Licence_Potential.[LuT Aktenzeichen] = [tbl_LuT Vorgang].[LuT Aktenzeichen] AND tbl_Licence_Potential.GJ = '08/09')
WHERE [tbl_LuT Vorgang].Reporting=True
AND [tbl_LuT Vorgang].[Bearbeiter LuT] = 'BOI';

Jet really has more nuances than out and out weaknesses. The required brackets in this instance are just an example of that.
(It is particular about joining precedence and requiring bracketing to disambiguate).

Obviously Jet has limitations - it's a File Server engine (and if I were Microsoft I wouldn't spend countless millions enhancing it either while I had SQL Server in the porfolio too) but for what it is, it does pretty OK.
As you've since gone on to use anyway, SQL Server wouldn't be so constrained in the syntax.

Cheers.