Hi,

I need to find out which products are not contained in the membership table. Something like this should do it! "SELECT "PRODID" FROM "PRODUCTS" WHERE "PRODID" NOT IN (SELECT DISTINCT "PRODID" FROM "PRODUCTGROUPMEMBERSHIP")"

So how do I create it in code? The "PRODUCTGROUPMEMBERSHIP" table is a relation of "PRODUCTS". Do I need to setup a relation collection and filter?

relations = New RelationCollection
relationsToWalk.Add(EntityClasses.PRODUCTSEntity.Relations.PRODUCTGROUPMEMBERSHIPEntityUsingPRODID)

filter.Add(New FieldCompareSetPredicate

Just can't work out how the filter should be from the Help and examples.

Thanks

Simon

You need a FieldCompareSetPredicate instance as the filter element. Set it up so it filters on productID in PRODUCTGROUPMEMBERSHIP, and set negate to true, then add it to a predicateexpression and use that as teh filter in GetMulti(). No relations required. (this will result in a subquery, which is often more efficient in scenario's like this)

You can also use relations to do this. In fact: SELECT DISTINCT PRODUCTS.* FROM "PRODUCTS" LEFT JOIN PRODUCTGROUPMEMBERSHIP ON PRODUCTS.PRODID = PRODUCTGROUPMEMBERSHIP.PRODID WHERE PRODUCTGROUPMEMBERSHIP.PRODID IS NULL

will also give the same data. To accomplish this, add the relation between product and PRODUCTGROUPMEMBERSHIP to a relationcollection, and use a CompareNull predicate on PRODUCTGROUPMEMBERSHIP.PRODID. Specify a joinhint when adding the relation to the relation collection (or set ObeyWeakRelations to true) and pass both the relationcollection and the filter to GetMulti()

Thanks for the prompt reply, but can you provide me with an example for your first explaination please?

I'm having difficulty setting up the filter.

Dim sorter As SortExpression = New SortExpression
sorter.Add(SortClauseFactory.Create(DAL.CTMS.REF_PRODUCTSFieldIndex.TITLE, SortOperator.Ascending))
Dim filter As IPredicateExpression = New PredicateExpression
filter.Add(new FieldCompareSetPredicate(dal.CTMS.REF_PRODUCTGROUPMEMBERSHIPFieldIndex.PRODUCTID, dal.CTMS.REF_PRODUCTSFieldIndex.PRODUCTID, SetOperator.NotEqual, nothing,True)

Dim fields As New ResultsetFields(2)
fields.DefineField(DAL.CTMS.REF_PRODUCTSFieldIndex.PRODUCTID, 0, "ProductID")
fields.DefineField(DAL.CTMS.REF_PRODUCTSFieldIndex.TITLE, 1, "Title")

Dim dtl As DataTable = New DataTable
Dim dao As TypedListDAO = New TypedListDAO
dao.GetMultiAsDataTable(fields, dtl, 0, sorter, Nothing, relations, False, Nothing, Nothing, 0, 0)
' Set the return value
ReadNonMembershipData = dtl

Many thanks Simon

IndianaJones wrote:

Thanks for the prompt reply, but can you provide me with an example for your first explaination please? I'm having difficulty setting up the filter.

filter.Add(new FieldCompareSetPredicate(dal.CTMS.REF_PRODUCTGROUPMEMBERSHIPFieldIndex.PRODUCTID, dal.CTMS.REF_PRODUCTSFieldIndex.PRODUCTID, SetOperator.NotEqual, nothing,True)

Replace the line above with:

filter.Add( _
    New FieldCompareSetPredicate( _
        EntityFieldFactory.Create(dal.CTMS.REF_PRODUCTGROUPMEMBERSHIPFieldIndex.PRODUCTID), _
        EntityFieldFactory.Create(dal.CTMS.REF_PRODUCTSFieldIndex.PRODUCTID), _
        SetOperator.In, _
        nothing, _
        True) _
        )

Also, see the FieldCompareSetPredicate documentation in the Filtering and sorting section, which does contain an example snippet how to use it.

The negate parameter negates your query, so you should just formulate your query normally (with the IN operator) and then set negate to true, which will result in NOT IN

Thankyou !