>Source data is an iSeries(AS400) machine
>
>I have one table that holds master header data and a child table that holds child records. My problem is when I chile record is flaged as deleted the record nolonger shows up in my join SPT list. Here is my sql:
>
>The deleteflag is a field of the webprddt7.qartdisps table.
>
>

>
>testconnString = "Select webprddt7.qartmast.recseq, docno, partno, rtqty, srcdef, DATE(idate) As datei," +;
>" webprddt7.qartmast.defects, webprddt7.qartdisps.disptype, webprddt7.qartdisps.rejsource," +;
>" webprddt7.qartdisps.dispqty, webprddt7.qartdisps.location, webprddt7.qartdisps.recseq," +;
>"  webprddt7.qartdisps.dispcmnts, locindex, splitmo, sourceflag, vstockflag, purgepflag, disploc," +;
>" localarw, dispwctr,webprddt7.qartdisps.buyerno, partname, corflag, caflag,webprddt7.qartdisps.vendorcode," +;
>" webprddt7.qartdisps.vendorname, rmarflag From webprddt7.qartmast" +;
>" Join webprddt7.qartsrcdef On webprddt7.qartsrcdef.recseq = webprddt7.qartmast.sourceflag" +;
>" Left Join webprddt7.qartdisps On webprddt7.qartdisps.rtindex = webprddt7.qartmast.recseq" +;
>" Left Join webprddt7.qartsubsrc On webprddt7.qartsubsrc.rtindex = webprddt7.qartmast.recseq" +;
>" Where webprddt7.qartmast.recseq = 767899 And (deleteflag = 0 Or deleteflag is NULL)"
>
>

Take a look at this blog post explaining your problem

http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx

The solution is to move the DeleteFlag condition to the JOIN condition.