I want to produce an SQL statement like the following, but have NO CLUE how to do it.

SELECT * FROM members WHERE mID IN 
    (SELECT t1.mID FROM Table t1 WHERE t1.cID = 1 AND t1.typeID=1 AND
         EXISTS (SELECT t2.* FROM Table t2 
         WHERE t1.mID = t2.mID AND t2.cID = 1 AND t2.typeID = 2))

I'm fairly new to LLBLGen. I know how to set up relationships and add predicates, but it's the EXISTS clause that has me stumped. Any help would be appreciated.

Thanks!

-Renée

POST UPDATED: I am using 'SelfServicing' implementation and C# code, if that matters at all. I see the example in the help section for the "FieldCompareSetPredicate" but I still don't understand how to adapt that to my needs. With 23 constructors it's hard to know what to try without some examples.

I came up with this, but it's throwing an error...

MemberCollection members = new MemberCollection();
            IPredicate pred = (new FieldCompareSetPredicate(MemberFields.MemberId, 
                BodyCompFields.MemberId, SetOperator.In, 
                ((BodyCompFields.ContestId == contestID) &
                                (BodyCompTypeFields.BodyCompTypeId == 1) &
                                (new FieldCompareSetPredicate(MemberFields.MemberId, 
                                    BodyCompFields.MemberId, 
                                    SetOperator.Exist, ((BodyCompFields.ContestId == contestID) &
                                    (BodyCompTypeFields.BodyCompTypeId == 2)))))));
members.getMulti(pred);

The VisualStudio Predicate Visualizer says the predicate is the following:

MemberEntity.[MemberId] IN 
(
    SELECT BodyCompEntity.[MemberId] FROM BodyCompEntity WHERE 
    (
        
        (
            BodyCompEntity.[ContestId] = @ContestId1 AND BodyCompTypeEntity.[BodyCompTypeId] = @BodyCompTypeId2
        )
        AND  EXISTS 
        (
            SELECT BodyCompEntity.[MemberId] FROM BodyCompEntity WHERE 
            (
                BodyCompEntity.[ContestId] = @ContestId3 AND BodyCompTypeEntity.[BodyCompTypeId] = @BodyCompTypeId4
            )
        )
    )

Which is EXACTLY what I want...but when it's run, it gives me the following error:

{"An exception was caught during the execution of a retrieval query: The multi-part identifier \"ContestMS.dbo.BodyCompType.BodyCompTypeID\" could not be bound.\r\nThe multi-part identifier \"ContestMS.dbo.BodyCompType.BodyCompTypeID\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

It's like it's trying to assign the inner EXISTS select subquery to another value... I just want a plain exists. Well, I don't know what's wrong with it...but I've tried a lot of combinations and can't seem to come up with something that works.

I've tried (referring to the second FieldCompareSetPredicate in the statement above)

  ....(new FieldCompareSetPredicate(BodyCompFields.BodyCompID, BodyCompFields.BodyCompID, ...

  ....(new FieldCompareSetPredicate(null,null, ...

  ....(new FieldCompareSetPredicate(BodyCompFields.MemberID, null, ...

Truth is I don't care what the select statement brings back... I just want to know if the second record exists.

Help?

Thanks, -Renée

You do: (BodyCompTypeFields.BodyCompTypeId == 2)))))));

which refers to BodyCompType entity, while your other predicates refer to BodyComp. This means that you've to specify a relation as well, as BodyCompType is a related entity

You run into the point where EXISTS queries are a bit harder to formulate than a typical IN query. EXISTS subqueries are almost always correlated subqueries (refer to elements in the outer query). This is done typically with aliassing. Your query code doesn't use aliassing.

Your EXISTS query also refers to BodyComp, which isn't good, as the setField is used to build the set (therefore, it's the setfield). So you should specify null (or whatever) for 'field', and alias the outer query. I've rewritten your code, it should work now:

MemberCollection members = new MemberCollection();
    IPredicate pred = (new FieldCompareSetPredicate(MemberFields.MemberId,
        BodyCompFields.MemberId, SetOperator.In,
        ((BodyCompFields.ContestId.SetObjectAlias("t2") == contestID) &
                        (BodyCompTypeFields.BodyCompTypeId == 1) &
                        (new FieldCompareSetPredicate(null,
                            BodyCompTypeFields.BodyCompTypeId,
                            SetOperator.Exist, ((BodyCompFields.ContestId.SetObjectAlias("t1") == contestID) &
                            (BodyCompTypeFields.BodyCompTypeId == 2)))))));

Please see the little differences.

Thank you so much for your help!