Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Question Unanswered: left outer join losing records without a match

    I have 2 tables GLSUMMARY and GLBUDGET, they are identical.
    I am joining them together with a left outer join from the SUMMARY to the BUDGET but when I dont have a matching BUDGET record on the join the SUMMARY gets dropped as well

    Any help will be appreciated!

    Here is the query!
    SELECT s.conu as CoNu, s.deptnu as DeptNu,
    s.fundnu as FundNu, s.acctnu as AcctNu,
    Sum(isNull(Amt01,0)) as Amt01,
    Sum(isNull(Amt02,0)) as Amt02,
    Sum(isNull(Amt03,0)) as Amt03,
    Sum(isNull(Amt04,0)) as Amt04,
    Sum(isNull(Amt05,0)) as Amt05,
    Sum(isNull(Amt06,0)) as Amt06,
    Sum(isNull(Amt07,0)) as Amt07,
    Sum(isNull(Amt08,0)) as Amt08,
    Sum(isNull(Amt09,0)) as Amt09,
    Sum(isNull(Amt10,0)) as Amt10,
    Sum(isNull(Amt11,0)) as Amt11,
    Sum(isNull(Amt12,0)) as Amt12,
    Sum(isNull(Amt13,0)) as Amt13,
    Sum(isNull(Bud01,0)) as Bud01,
    Sum(isNull(Bud02,0)) as Bud02,
    Sum(isNull(Bud03,0)) as Bud03,
    Sum(isNull(Bud04,0)) as Bud04,
    Sum(isNull(Bud05,0)) as Bud05,
    Sum(isNull(Bud06,0)) as Bud06,
    Sum(isNull(Bud07,0)) as Bud07,
    Sum(isNull(Bud08,0)) as Bud08,
    Sum(isNull(Bud09,0)) as Bud09,
    Sum(isNull(Bud10,0)) as Bud10,
    Sum(isNull(Bud11,0)) as Bud11,
    Sum(isNull(Bud12,0)) as Bud12,
    Sum(isNull(Bud13,0)) as Bud13
    FROM shelbydb.shelby.GLSummary S
    left OUTER JOIN shelbydb.shelby.GLBudget B
    on
    (s.begindate = b.begindate)
    and (s.acctnu = b.acctnu)
    and (s.conu = b.conu)
    and (s.deptnu = b.deptnu)
    and (s.fundNu = b.fundNu)
    WHERE
    (s.begindate = '1/1/2004'
    and b.begindate = '1/1/2004')
    group by
    S.conu, S.deptnu, S.fundnu, S.acctnu,
    b.conu, b.deptnu, b.fundnu, b.acctnu

  2. #2
    Join Date
    May 2004
    Posts
    125
    It has to do with your where clause. Since you want everything in the s table and s.begindate = b.beginedate when you say b.begindate = 1/1/2004 you are filtering out the s rows that do not have a matching b row because b.begindate = null. Try this query out:

    SELECT s.conu as CoNu, s.deptnu as DeptNu,
    s.fundnu as FundNu, s.acctnu as AcctNu,
    Sum(isNull(Amt01,0)) as Amt01,
    Sum(isNull(Amt02,0)) as Amt02,
    Sum(isNull(Amt03,0)) as Amt03,
    Sum(isNull(Amt04,0)) as Amt04,
    Sum(isNull(Amt05,0)) as Amt05,
    Sum(isNull(Amt06,0)) as Amt06,
    Sum(isNull(Amt07,0)) as Amt07,
    Sum(isNull(Amt08,0)) as Amt08,
    Sum(isNull(Amt09,0)) as Amt09,
    Sum(isNull(Amt10,0)) as Amt10,
    Sum(isNull(Amt11,0)) as Amt11,
    Sum(isNull(Amt12,0)) as Amt12,
    Sum(isNull(Amt13,0)) as Amt13,
    Sum(isNull(Bud01,0)) as Bud01,
    Sum(isNull(Bud02,0)) as Bud02,
    Sum(isNull(Bud03,0)) as Bud03,
    Sum(isNull(Bud04,0)) as Bud04,
    Sum(isNull(Bud05,0)) as Bud05,
    Sum(isNull(Bud06,0)) as Bud06,
    Sum(isNull(Bud07,0)) as Bud07,
    Sum(isNull(Bud08,0)) as Bud08,
    Sum(isNull(Bud09,0)) as Bud09,
    Sum(isNull(Bud10,0)) as Bud10,
    Sum(isNull(Bud11,0)) as Bud11,
    Sum(isNull(Bud12,0)) as Bud12,
    Sum(isNull(Bud13,0)) as Bud13
    FROM shelbydb.shelby.GLSummary S
    left OUTER JOIN shelbydb.shelby.GLBudget B
    on
    (s.begindate = b.begindate)
    and (s.acctnu = b.acctnu)
    and (s.conu = b.conu)
    and (s.deptnu = b.deptnu)
    and (s.fundNu = b.fundNu)
    WHERE
    s.begindate = '1/1/2004'
    group by
    S.conu, S.deptnu, S.fundnu, S.acctnu,
    b.conu, b.deptnu, b.fundnu, b.acctnu
    Last edited by DMWCincy; 12-01-04 at 00:01.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    FROM shelbydb.shelby.GLSummary S
    left OUTER JOIN shelbydb.shelby.GLBudget B
    on
    s.begindate = b.begindate
    and s.acctnu = b.acctnu
    and s.conu = b.conu
    and s.deptnu = b.deptnu
    and s.fundNu = b.fundNu
    and b.begindate = '1/1/2004'
    WHERE
    s.begindate = '1/1/2004'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2004
    Location
    Riverside, CA
    Posts
    21

    Thumbs up Right on as always!

    Thanks you guys for your support and sharing your expertise!
    -R

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •