Get parts and its associated library using API's only

[DarshanT]

Hello,

I have following SQL

select master.wtpartnumber, lib.NAMECONTAINERINFO, lib.IDA2A2 from wtpartmaster master, wtlibrary lib where master.wtpartnumber not like '%-R' 
  and master.IDA3CONTAINERREFERENCE = lib.IDA2A2
  and lib.NAMECONTAINERINFO not like '%test%'

I want to use this SQL in Windchill API's but I'm unsuccessful so far, here is what I have so far:

        final QuerySpec qs = new QuerySpec();

        int lib = qs.addClassList(WTLibrary.class, false);
        int mastInt = qs.addClassList(WTPartMaster.class, false);

        ClassAttribute ca = new ClassAttribute(WTPartMaster.class, WTPartMaster.NUMBER);

        ClassAttribute ca1 = new ClassAttribute(WTLibrary.class, WTLibrary.CONTAINER_ID);

        qs.appendSelect(ca, new int[]{0}, false);
        qs.appendSelect(ca1, new int[]{1}, false);

        qs.appendWhere(new SearchCondition(WTLibrary.class, WTLibrary.NAME, SearchCondition.NOT_LIKE, "%test%"), new int[]{0});

        qs.appendJoin(mastInt, WTPartMaster.CONTAINER_ID, lib);        
        // Below condition through error
        //qs.appendAnd();
        //qs.appendWhere(new SearchCondition(WTPartMaster.class, WTPartMaster.CONTAINER_ID, SearchCondition.EQUAL, WTLibrary.CONTAINER_ID), new int[]{0});

        System.out.println("\n\n" + qs.toString() + "\n\n" + qs.getWhere() + "\n\n");

Can someone help me how to write the above SQL in API's?

Thanks,

Manjunath Reddy

[DarshanT · Accepted solution]

final QuerySpec qs = new QuerySpec();
int lib = qs.addClassList(WTLibrary.class, false);
int mastInt = qs.addClassList(WTPartMaster.class, false);
qs.setAdvancedQueryEnabled(true);
qs.appendWhere(new SearchCondition(WTLibrary.class, WTLibrary.NAME, SearchCondition.NOT_LIKE, "%test%"), new int[]{lib});
qs.appendAnd();
qs.appendWhere(new SearchCondition(WTPartMaster.class, "containerReference.key.id", WTLibrary.class, "thePersistInfo.theObjectIdentifier.id"), new int[]{mastInt, lib});