Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: display all rows in left outer join (was "Would be so grateful for sql help")

    This is what I want my results to look like
    Code:
    invoice     wbs1          wbs2      amount
    8060        000-333       500           0
    8060        000-333       100           0
    8060        000-333       140           0
    8060        000-333       150         4335 
    8060        000-333       160           0
    8267        000-333       500           0
    8267        000-333       100         20500
    8267        000-333       140         547.50
    8267        000-333       150         2000
    8267        000-333       160         5000
    This is what I have so far:
    Code:
    SELECT  PR.WBS1, PR.WBS2, ledgerar.invoice, SUM(CASE WHEN ledgerar.transtype = 'in' AND ledgerar.period = '200405' THEN ledgerar.amount * - 1 END)  AS amount
    FROM         PR LEFT OUTER JOIN
                          LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND LedgerAR.WBS3 = PR.WBS3
    WHERE     (LedgerAR.wbs1 = '001-333') AND ledgerar.period = '200405'
    GROUP BY pr.wbs1, pr.wbs2, ledgerar.invoice
    the above query gives me the following results:
    Code:
    invoice       wbs1        wbs2       amount
    8060         000-333     100           0
    8060         000-333     140           0
    8060         000-333     160         4335
    8267         000-333     100         1320
    8267         000-333     140         20912.5
    8267         000-333     150         8363
    8267         000-333     160         2650
    But I don't know how to get the query to display all of wbs2 whether it is null or not. So, for each invoice number there should be five records according to the wbs2 codes (500, 100, 140, 150, 160)

    Does anyone know how to do this?

    Thanks,
    Laura

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change WHERE to AND
    Code:
    select PR.WBS1
         , PR.WBS2
         , LedgerAR.invoice
         , sum(case when LedgerAR.transtype = 'in' 
                     and LedgerAR.period = '200405' 
                    then LedgerAR.amount * -1 end)  as amount
      from PR 
    left outer 
      join LedgerAR 
        on LedgerAR.WBS1 = PR.WBS1 
       and LedgerAR.WBS2 = PR.WBS2 
       and LedgerAR.WBS3 = PR.WBS3
       and LedgerAR.wbs1 = '001-333'
       and LedgerAR.period = '200405'
    group 
        by PR.WBS1
         , PR.WBS2
         , LedgerAR.invoice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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