SQL Inner/Outter Multi-Join

[sbatschelet]

I have been banging my head on this one for 2 days I might just need a
fresh set of eyes. This query lists all of the sales categories which
are listed in POSCat, more specifically POSCat.Description. It then
sums these categories as they appear in the sales history table
InvLine. What I like about this layout is that even if there is a null
sum value for a category it still will be listed and I can use
something like iif to make it $0.00.

Select POSCat.*,(Select Sum(InvLine.Price) From InvLine Where
POSCat.Description = InvLine.Cat) as Totals
FROM POSCat

The recordset looks like this

cat1 sumcat1
cat2 sumcat2

Now although that works on some levels its very limited and only gives
me a total for the table. What I really need is to verify that the
item has been paid for by comparing the invoice total to invoice paid,
also be able to search by date. What I came up with is this:

SELECT InvLine.Cat, SUM(InvLine.Price) AS Total
FROM Payments
INNER JOIN ((InvLine INNER JOIN Invoice ON Invoice.IId = InvLine.IId)
INNER JOIN PaySplit ON Invoice.IId = PaySplit.IId) ON Payments.PAYId =
PaySplit.PayId
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate BETWEEN
#1/2/03# AND #1/2/03#
GROUP BY InvLine.Cat
ORDER BY InvLine.Cat Asc

The recordset looks like this if there was values only in those
categories 1, 4, 5

cat1 sumcat1
cat4 sumcat4
cat5 sumcat5

Which works great except it only lists the categories which are not
null but I would like to list all of them like the first query does.

After a little help I realized that I needed to do an Outer Join

I tried this a few diffent ways but Access is complaining that the join
is not supported, so my SQL must be setup wrong. Here is what I have
so far, again any help is appreciated.

SELECT POSCat.Description, Sum(Payments.Value) AS Total
FROM POSCat LEFT OUTER JOIN (Payments INNER JOIN ((InvLine INNER JOIN
Invoice ON InvLine.IId = Invoice.IId) INNER JOIN PaySplit ON
Invoice.IId = PaySplit.IId) ON Payments.PAYId = PaySplit.PayId) ON
POSCat.Description = InvLine.Cat
WHERE Invoice.Total = Invoice.Paid AND Payments.PayDate Between
#3/20/2006# And #3/22/2006#
GROUP BY POSCat.Description
ORDER BY POSCat.Description Asc

 

[John Spencer]

Try dropping the "Outer" word and just use "LEFT JOIN".

 

[tranceport185]

Hi actually didn't try that but it gave me the same error its not that
Access doesn't support LEFT OUTER JOIN (well from what I have read at
least) its that it doesn't like the way I did it. But thanks for the
post.