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 = ' '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|||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 ().|||[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 ;)|||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|||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 = ' ')|||i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:

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|||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|||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)|||I didn't realize I could do that. Thanks!!sql