Multiple Criteria Join ON

Posts   
 
    
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 13-May-2005 20:46:52   

Does anyone know if there is there a way to express something like the following in my relations/predicates?

select * from tblLetter
    JOIN tblUserLetter ON tblLetter.LetterID = tblUserLetter.LetterID
    LEFT JOIN tblCategoryLetter ON (tblLetter.LetterID = tblCategoryLetter.LetterID AND tblCategoryLetter.CategoryID = 7)
WHERE tblUserLetter.UserID = 3
    AND tblCategoryLetter.CategoryID is null

The part that I am trying to figure out how to implement is the

        _**LEFT JOIN tblCategoryLetter ON (tblLetter.LetterID = tblCategoryLetter.LetterID AND tblCategoryLetter.CategoryID = 7)**_

I am trying to pull Letters for User that are not already in a certain category...

I'm wondering if there is some other way that I can just do this in the predicateexpression itself, but can't really figure it out.

Any help would be greatly appreciated.

Best regards,

Hal

hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 14-May-2005 01:20:58   

OK - I have acheived the desired result by using a FieldCompareSetPredicate.

So now my SQL code would look more like this:

select * from tblLetter
    JOIN tblUserLetter ON tblLetter.LetterID = tblUserLetter.LetterID
                WHERE tblUserLetter.UserID = 3 
                            AND NOT tblUserLetter.LetterID in 
                            (select LetterID from tblCategoryLetter where CategoryID = 7)

Thanks and if anyone does seem any problems with the above solution or if in fact there is away to add criteria to the JOIN statement, it would be great to know.

Best regards,

Hal

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-May-2005 11:21:57   

You can specify extra filters for the join clause, please check: "using the generated code / selfservicing or adapter / Filtering and sorting -> Custom filters for EntityRelations" simple_smile

Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 19-May-2005 05:20:56   

Otis wrote:

You can specify extra filters for the join clause, please check: "using the generated code / selfservicing or adapter / Filtering and sorting -> Custom filters for EntityRelations" simple_smile

That did the trick... thanks as always for the help...

Hal