HomeActive Query BuilderWorking With Left Outer Joins In Code

Working With Left Outer Joins In Code


I'm trying to join two tables (A and B) together, in .NET code so that the join is a LEFT OUTER join.

I think I'm close - I have a Link object that I get from 'Query.AddLink) and I set it's LeftType 'LinkSideType.Outer'

And it does make a LEFT OUTER join; but I have criteria on the B table that still appears in the 'WHERE' clause that removes the records I'd hoped to get.

I want something like this:
SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID AND B.Value > 0 WHERE A.ID = 2

But I'm getting:
SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID WHERE A.ID = 2 AND B.Value > 0

Can someone point me in the correct direction. I think I can do (B.Value > 0 OR B.Value IS NULL); but I think it makes the SQL harder to read and understand.
Rob | email
September 16 2010, 05:41 PM
Rob,

You can add another link with the second condition:

link2 = Query.AddLink(link1.LeftDatasource, link1.RightDatasource, "b.Value > 0");

...or directly modify link1.LinkExpressionString property.
Eugene Starkov | email
4 days, 8 hours since original post
Thank you - that makes perfect sense.
Rob | email
4 days, 16 hours since original post
I have version 1.14 and there is no AddLink overload that takes 3 parameters like this and LinkExpressionString does not exist.

What am I missing?
Pierre Alain | email
5 months, 1 week since original post
There are four public AddLink() overloads with different params. Please ensure you have the latest version.
Eugene Starkov | email
5 months, 1 week since original post
Please forgive me. Somehow, Visual Studio was saying we were referencing version 1.14, but it seems it was still an older version. Once I recreated the reference, it compiled fine.

Thank you.
Pierre Alain | email
5 months, 1 week since original post
This topic is closed