Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Unhappy Unanswered: 3 table left outer join

    I am joining 3 tables. 1st table I want all rows; I also want all rows in table 1 not in table 2 or in Table 2 but have a blank journal id and all rows in table 1 not in table 3. HELP!

    SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
    B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
    FROM PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
    ON A.BUSINESS_UNIT = C.GROUP_BU
    AND A.INVOICE = C.ITEM
    PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
    ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND D.INVOICE = A.INVOICE
    AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND B.INVOICE = C.ITEM
    WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
    AND B.JOURNAL_ID = ' '

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you post your query again, there seems to be a typo in it

    AND A.INVOICE = C.ITEM
    PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B

    the PS_BI_HDR D is wrong where it is


    rudy

  3. #3
    Join Date
    Nov 2002
    Posts
    7
    SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
    B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
    FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
    ON A.BUSINESS_UNIT = C.GROUP_BU
    AND A.INVOICE = C.ITEM)
    PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
    ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND D.INVOICE = A.INVOICE
    AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND B.INVOICE = C.ITEM
    WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
    AND B.JOURNAL_ID = ' '

    This is the query. Note: ITEM and INVOICE are on in the same on two different tables. I forgot the ().

  4. #4
    Join Date
    Nov 2002
    Posts
    7
    [QUOTE][SIZE=1]Originally posted by AMYC
    SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
    B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
    FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
    ON A.BUSINESS_UNIT = C.GROUP_BU
    AND A.INVOICE = C.ITEM)
    PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
    ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
    AND D.INVOICE = B.INVOICE
    WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
    AND B.JOURNAL_ID = ' '

    Update; found one error

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i still see a syntax error in the following snippet of code --

    FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
    ON A.BUSINESS_UNIT = C.GROUP_BU
    AND A.INVOICE = C.ITEM)
    PS_BI_HDR D LEFT OUTER JOIN ...

    this is structurally the same as

    FROM (derived table) foo bar LEFT OUTER JOIN ...

    you can give the derived table one alias name but not two


    rudy
    Last edited by r937; 11-07-02 at 12:51.

  6. #6
    Join Date
    Nov 2002
    Posts
    7
    Thanks for your help but I feel like I have bigger issues than syntax errors. It's more structural. This is a new approach I am trying but I still get rows I should not ie rows from BI_ACCT_ENTRY that should not be returned. THanks!

    SELECT A.BUSINESS_UNIT, A.INVOICE
    FROM PS_BI_HDR A WHERE (NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM PS_PENDING_ITEM_FS B
    WHERE A.BUSINESS_UNIT = B.GROUP_BU AND A.INVOICE = B.ITEM)
    AND NOT EXISTS(SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
    WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.INVOICE))
    OR EXISTS (SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
    WHERE JOURNAL_ID = ' ')

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:

    Code:
    SELECT A.BUSINESS_UNIT
         , A.INVOICE
      FROM PS_BI_HDR A 
     WHERE ( 
           NOT EXISTS 
             ( SELECT B.GROUP_BU
                    , B.ITEM 
                 FROM PS_PENDING_ITEM_FS B
                WHERE A.BUSINESS_UNIT = B.GROUP_BU 
                  AND A.INVOICE = B.ITEM )
       AND NOT EXISTS
             ( SELECT BUSINESS_UNIT
                    , INVOICE 
                 FROM PS_BI_ACCT_ENTRY C
                WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT 
                  AND A.INVOICE = C.INVOICE )
           )
        OR EXISTS 
             ( SELECT BUSINESS_UNIT
                    , INVOICE 
                 FROM PS_BI_ACCT_ENTRY C 
                WHERE JOURNAL_ID = ' ')
    now you can plainly see that you will get PS_BI_HDR records if any PS_BI_ACCT_ENTRY entries exist with a blank JOURNAL_ID

    was that your problem?

    by the way, i would not alias the table in the 3rd subquery with C because you've already used that in the 2nd subquery -- not that it causes an error, but you never know...

    rudy

  8. #8
    Join Date
    Nov 2002
    Posts
    7

    Talking Thanks

    I do format my code but just copying and pasting here as it is a total pain to format it on here anyway, I fixed it. Thanks!

    SELECT A.BUSINESS_UNIT, A.INVOICE
    FROM PS_BI_HDR A WHERE
    EXISTS (SELECT C.BUSINESS_UNIT, C.INVOICE FROM
    PS_BI_ACCT_ENTRY C
    WHERE JOURNAL_ID = ' '
    AND NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM
    PS_PENDING_ITEM_FS B
    WHERE A.BUSINESS_UNIT = B.GROUP_BU
    AND A.INVOICE = B.ITEM)
    OR NOT EXISTS(SELECT BUSINESS_UNIT,
    INVOICE FROM PS_BI_ACCT_ENTRY C
    WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.INVOICE = C.INVOICE)
    ORDER BY A.INVOICE

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is a total pain to format it on here
    not really

    just wrap it in code tags -- i typically type them, but you can also use the number/hash sign # button (between the IMG and PHP buttons when you create a post)

  10. #10
    Join Date
    Nov 2002
    Posts
    7

    format

    I didn't realize I could do that. Thanks!!

Posting Permissions

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