exists statement over two key fields

Posts   
 
    
PAZIMLI
User
Posts: 82
Joined: 01-May-2008
# Posted on: 23-Oct-2008 17:20:56   

Adapter llblgen Pro V2.5 C#.net 2005 Oracle 10g, SqlServer 2000, MySql 5.0

I try to write a query like;

select * from siparis_kalemi s where not exists (select * from acilan_is_emri where acilan_is_emri.musteri_siparis_no = s.SIPARIS_NO and acilan_is_emri.MUSTERI_SK_NO = s.SK_NO ) and s.SK_DURUMU = 2

I tried this but i could not find joining the tables over two fields.

            filter2.Add(new FieldCompareSetPredicate(
             SiparisKalemiFields.SkNo, null, AcilanIsEmriFields.MusteriSkNo, null,
              SetOperator.In, (AcilanIsEmriFields.MusteriSiparisNo == SiparisKalemiFields.SiparisNo) &
                            (AcilanIsEmriFields.MusteriSkNo == SiparisKalemiFields.SkNo) &
                            (AcilanIsEmriFields.IsEmriNo == UretimIsEmriFields.IsEmriNo) &
                            (SiparisKalemiFields.SkDurumu == (decimal)2)
                            , true));
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 24-Oct-2008 05:16:52   

First, consider this to make an "EXISTS/NOT EXISTS":

FieldCompareSetPredicates also allows you to define EXISTS () queries. It is then not necessary to specify an IEntityField2 object with the predicate's constructor (specify null / nothing) as it is ignored when building the SQL.

Then, I see one more entitiy in your filter (UretimIsEmriFields). To be able to filter on this table you should include a RelationCollection that include the relevant relation.

Here is an example: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11951

David Elizondo | LLBLGen Support Team