Sorry, I know this has been covered in other threads but I'm struggling to work out how to get this to work. Naturally, I get the message about cannot have outer joins where there are more than two tables. I sort of get the principal but not the practicality of doing it in SQL.
What I have are three tables from Sage50 Accounts. The Sales Ledger, Invoice and Invoice Item tables. I need to return all the sales ledger accounts whether they have an invoice or not in a specific period (hence the parameters).
Using inner joins the SQL statement looks like this
SELECT
For the life of me, whatever I try I cannot work out the correct change to get a left outer join between the SALES_LEDGER and INVOICE table plus an inner join between the INVOICE and INVOICE_ITEM tables. Can anyone help? I'll add you do my list of Excel deities if you can.
What I have are three tables from Sage50 Accounts. The Sales Ledger, Invoice and Invoice Item tables. I need to return all the sales ledger accounts whether they have an invoice or not in a specific period (hence the parameters).
Using inner joins the SQL statement looks like this
SELECT
SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.NAME, INVOICE.INVOICE_TYPE, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE_ITEM.STOCK_CODE, INVOICE_ITEM.QUANTITY, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.DISCOUNT_RATE, INVOICE_ITEM.DISCOUNT_AMOUNT, INVOICE_ITEM.NET_AMOUNT, SALES_LEDGER.ANALYSIS_1, SALES_LEDGER.ANALYSIS_2, SALES_LEDGER.ANALYSIS_3, INVOICE_ITEM.DESCRIPTION
FROM
INVOICE INVOICE, INVOICE_ITEM INVOICE_ITEM, SALES_LEDGER SALES_LEDGER
WHERE
INVOICE.INVOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER
AND
SALES_LEDGER.ACCOUNT_REF = INVOICE.ACCOUNT_REF
AND
((INVOICE.INVOICE_DATE>=? And INVOICE.INVOICE_DATE<=?)
AND
(INVOICE.INVOICE_TYPE Not Like "Service%") AND (INVOICE_ITEM.NET_AMOUNT<>0))
ORDER BYAND
SALES_LEDGER.ACCOUNT_REF = INVOICE.ACCOUNT_REF
AND
((INVOICE.INVOICE_DATE>=? And INVOICE.INVOICE_DATE<=?)
AND
(INVOICE.INVOICE_TYPE Not Like "Service%") AND (INVOICE_ITEM.NET_AMOUNT<>0))
SALES_LEDGER.ACCOUNT_REF, INVOICE_ITEM.STOCK_CODE
For the life of me, whatever I try I cannot work out the correct change to get a left outer join between the SALES_LEDGER and INVOICE table plus an inner join between the INVOICE and INVOICE_ITEM tables. Can anyone help? I'll add you do my list of Excel deities if you can.
Last edited: