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 - 1553 days ago
Last viewed on: 24 Apr 2017 (2923 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

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Left Join with subselects on both files
Posted: 5 years 3 months 21 days 13 hours 37 minutes ago
Edited: Thu, 05 Jan, 2012 at 09:31:35 (1937 days ago)

Think of the result of the outer join as a temporary table. You have some rows in the temp table where A and B had matching INTKEY, and the temp table has column data from both. You may also have rows in the temp table where INTKEY didn't exist in B, so the temp table has all NULLs for the B columns.

Next you apply the WHERE condition to the result, i.e., to the temporary table. For the non-matching records, the B columns are all NULL, so the date test fails.

Instead of testing the date it in the WHERE clause, try putting it in the join conditions:

insert into OUTFILE
    select A.FLD1, A.FLD2, A.FLD3, B.FLD4
    from FILEA A
    left outer join FILE B
      on (A.INTKEY = B.INTKEY AND B.FLD5 between :StartDate and :EndDate)
    where exists(select 1 from CTLFILE C where  A.FLD1 = C.FLD1)

You could also rewrite it using a CTE, which might make it more obvious what is happening:

insert into OUTFILE
    with B as (select INTKEY, FLD4 from FILEB
                 where FLD5 between :StartDate and :EndDate)
    select A.FLD1, A.FLD2, A.FLD3, B.FLD4
    from   FILEA A
    left outer join
           B on (A.INTKEY = B.INTKEY)
    where exists(select 1
                 from   CTLFILE C
                 where  A.FLD1 = C.FLD1)

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Left Join with subselects on both files
Posted: 5 years 3 months 21 days 13 hours 35 minutes ago

And, as always, I'd try running the SELECT separately, to make sure you're getting the expected rows, before turning it into an INSERT statement.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Left Join with subselects on both files
Posted: 5 years 3 months 21 days 11 hours 6 minutes ago

Yup - I always run interactive SQL (obviously replacing the substitution variables with literals), until I get the result I'm expecting, and then drop it into embedded SQL (occassionally forgetting to replace the literals with replacement variables - and then wondering why it always does the same thing regardless of program call parameters!!).

Well thanks, that appears to have gotten the desired result.