I've searched the Forums for the subject with 6 hits that include this reference only in the stack trace...and because of that I'm not sure that I'm using the correct tool to solve my little issue.

SQL Server 2005 query

select m1.title from movie as m1 where not exists (select m2.title from movie as m2 inner join rating as r1 on r1.RatingMovieid = m2.movieid inner join [User] as u1 on u1.Userid = r1.ratingUserId where u1.username = 'Carl' and m1.title = m2.title)

What do you think? Should I be using DatabaseSpecificCreator.CreateSubQuery or is there a better approach?

Thanks much.

Carl

Hi Carl,

Have a look for FieldCompareSetPredicate in the documentation as this is what I'm using to create a NOT EXISTS expression. It actually creates a NOT IN query but it's the same difference and note that there's a "negate" parameter which when set to true will add the NOT.

Cheers, Chris

Hello Chris,

Thank you for your response.

I have this query already established using the SetOperator.In parameter to the FieldCompareSetPredicate object. This works just fine...

// this works, but looking for a better approach that is more efficent for the indexes // bucket.PredicateExpression.Add(new FieldCompareSetPredicate( // MovieFields.MovieId,null,RatingFields.RatingMovieId, null, SetOperator.In, (RatingFields.RatingUserId == MovieGuid), true));

...the reason I'm honing this is that in a large table the NOT IN operation is very index 'unfriendly'. Hoping this table bloats!

Hi from this side of the 'pond'.

Carl

Carl,

What about this as an alternative:

select m1.title from movie as m1 left outer join rating as r1 on r1.RatingMovieid = m1.movieid left outer join [User] as u1 on u1.Userid = r1.ratingUserId and u1.username = 'Carl' where u1.Userid is null

Hello from this side of the 'pond' although most of the 'pond' seems to falling on us at the moment

Chris

Chris,

Your suggestion of the left join was right on! Thank you so much. The code follows that underscores how beautiful simple is. Again thanks so much. Carl

PredicateExpression pred = new PredicateExpression(); pred.Add(RatingFields.RatingUserId == MovieGuid); IRelationPredicateBucket bucket = new RelationPredicateBucket();

        bucket.Relations.Add(MovieEntity.Relations.RatingEntityUsingRatingMovieId,JoinHint.Left).CustomFilter = pred;
        bucket.PredicateExpression.Add(RatingFields.RatingUserId == DBNull.Value);

More than welcome (I'm a lot more familiar with SQL than LLBLGen so it was an ideal one for me).

Have a good weekend