I'm stuck trying to build the following query...

select * from Contact join (select contact_fk from contact_group where contact_group_list_fk in (8, 7, 6) and contact_group_list_fk not it (2, 3) group by contact_fk having count(*) = 3) qualifier on contact.contact_id = qualifier.contact_fk

I've been able to build the inner query using dynamic lists, but I don't know how to tie it in to the outer query. Here's my code so far...

        private ContactCollection test(ArrayList posTags, ArrayList negTags)
        {
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(ContactGroupFieldIndex.ContactFk, 0, "ContactFk");
            fields.DefineField(ContactGroupFieldIndex.ContactFk, 1, "ContactCount");
            fields[1].AggregateFunctionToApply = AggregateFunction.Count;

            IPredicateExpression innerFilter = new PredicateExpression();

            if (posTags.Count > 0)
            {
                IGroupByCollection groupByClause = new GroupByCollection();
                groupByClause.Add(fields[0]);
                IPredicateExpression having = new PredicateExpression();
                having.Add(new FieldCompareValuePredicate(fields[1], ComparisonOperator.Equal, posTags.Count));
                groupByClause.HavingClause = having;
                innerFilter.Add(PredicateFactory.CompareRange(ContactGroupFieldIndex.ContactGroupListFk, posTags));
            }

            if (negTags.Count > 0)
            {
                innerFilter.Add(PredicateFactory.CompareRange(ContactGroupFieldIndex.ContactGroupListFk, true, negTags));
            }

            DataTable dynamicList = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dynamicList, 0, null, innerFilter, null, true, groupByClause, null, 0, 0);
            return;

            ContactCollection cc = new ContactCollection();
            IPredicateExpression filter = new PredicateExpression();
            // now what?            
            
            return cc;
        }

Where do I go from here?

Thanks in advance, Bill

I think your query can be re-written as follows:

select *
from Contact 
Where contact_id IN
(select contact_fk
from contact_group
where contact_group_list_fk in (8, 7, 6) 
and contact_group_list_fk not it (2, 3)
group by contact_fk
having count(*) = 3)

In that case you should be using FieldCompareSetPredicate

Please refer to "Using the generated code ->Adapter/SelfServicing -> Filtering and Sorting -> The predicate system" in the LLBLGen Pro manual.

And you would find many samples on the forums if you search for FieldCompareSetPredicate.

Good Luck