Midrange News for the IBM i Community


Posted by: neilrh
A/P
Jackson, MI
Left Join with subselects on both files
has no ratings.
Published: 05 Jan 2012
Revised: 23 Jan 2013 - 2185 days ago
Last viewed on: 14 Jan 2019 (3691 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Left Join with subselects on both files Published by: neilrh on 05 Jan 2012 view comments(3)

I need to join records from 2 files (actually 3, but 2 is a good starting point and I can probably figure out how to add more files) for all records in fileA.

 

insert into OUTFILE
    select A.FLD1, A.FLD2, A.FLD3, B.FLD4
    from   FILEA A
    left outer join
           FILEB B on A.INTKEY = B.INTKEY
    where exists(select 1
                 from   CTLFILE C
                 where  A.FLD1 = C.FLD1)

 

 So I got the above to work, BUT I need to expand the where clause such that: 

where exists(select 1
             from   CTLFILE C
             where  A.FLD1 = C.FLD1)
AND   B.FLD5 between :StartDate and :EndDate

 

 Now all I get is an inner join, I lose the non-matching records from FILEA.  What did I miss?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS