The following query works ok:

SELECT Categories.CategoryID, Sum(InvoiceDetails.Amount) AS tot
FROM Categories, InvoiceDetails, Invoices
WHERE Categories.CategoryID = InvoiceDetails.CategoryID
AND Invoices.InvoiceID = InvoiceDetails.InvoiceID
AND Invoices.ReturnID = '4090604'
AND Categories.ResaleOhdTakings = 'R'
GROUP BY Categories.CategoryID

except that if there are no InvoiceDetails for a particular
CategoryID then that CategoryID is not displayed. I would like the
results to display a record for every CategoryID.

I feel a need for a LEFT JOIN but however much I try I just cannot
get it to work. Been playing with the problem for a few weeks without
success.

Any solution much appreciated. Many thanks.

Clive Astley

__________________
Clive Astley

Try adding "OR" and Is Null to your category

SELECT Categories.CategoryID, Sum(InvoiceDetails.Amount) AS tot
FROM Categories, InvoiceDetails, Invoices
WHERE Categories.CategoryID = InvoiceDetails.CategoryID
AND Invoices.InvoiceID = InvoiceDetails.InvoiceID
AND Invoices.ReturnID = '4090604'
AND Categories.ResaleOhdTakings = 'R'
OR Categories.Categoryid Is Null
GROUP BY Categories.CategoryID


Jaime E. Maccou
Applications Analyst

Many thanks for your help Jaime. I had to put your suggestion in a different line to avoid the Categories.ResaleOhdTakings = 'R' criteria being ignored, so as follows:

SELECT Categories.CategoryID, Sum(InvoiceDetails.Amount) AS tot
FROM Categories, InvoiceDetails, Invoices
WHERE Categories.CategoryID = InvoiceDetails.CategoryID
AND Invoices.InvoiceID = InvoiceDetails.InvoiceID
AND Invoices.ReturnID = '4090604'
OR Categories.Categoryid Is Null
AND Categories.ResaleOhdTakings = 'R'
GROUP BY Categories.CategoryID

This does what I need but it is sooooooooooo slow. 3.5 minutes on an ordinary PC compared with pretty well instantaneous before the amendment. The categories table has less than 20 entries and the InvoiceDetails about 3000. Any ideas would be appreciated.

Best wishes,


Clive Astley

Hi Clive

Sorry, but I added some comments in the other forum that you may find of interest.

Your use of "OR" I think has destroyed the meaning of your query, as "AND" usually takes precedence so your query is in fact (note the additional brackets):

SELECT Categories.CategoryID, Sum(InvoiceDetails.Amount) AS tot
FROM Categories, InvoiceDetails, Invoices
WHERE (Categories.CategoryID = InvoiceDetails.CategoryID
AND Invoices.InvoiceID = InvoiceDetails.InvoiceID
AND Invoices.ReturnID = '4090604' )
OR (Categories.Categoryid Is Null
AND Categories.ResaleOhdTakings = 'R')
GROUP BY Categories.CategoryID

Ignoring the first set of brackets and concentrating on the second, you will note that there are no binding conditions between the tables. This would mean that every record in CATEGORIES (20 entries) would be matched aginst every record in INVOICEDETAILS (3000) and in turn against every record in INVOICES (say, 50 - I think that this would probably be higher, but bear with me). So you now have a table containing 20 * 3000 * 50 = 3 million records!!! This is why your query is slow.

The use of "OR Categories.Categoryid Is Null" is puzzling unless you are trying to include categories without an invoice in which case I would delete the line and replace your FROM statement with:

FROM categories
LEFT INNER JOIN InvoiceDetails ON Categories.CategoryID = InvoiceDetails.CategoryID
LEFT INNER JOIN Invoices ON Invoices.InvoiceID = InvoiceDetails.InvoiceID

This will include all categories irrespective of whether there are any corresponding records in InvoiceDetails or Invoices.

You could also try applying unique indexes to all ???ID type fields in your database.

Best wishes
Marcus.
--
All suggestions given are just that, we accept no liability for or guarantee any
suggestions made. Any changes to your system or business decisions are taken entirely at your own risk.

Hi Marcus. Thanks for your response. I had already read/responded to the other forum before reading this.

The NULL line resulted from Jaime's suggestion.

I actually want to just sum all the Amounts by Category in the InvoiceDetails table. But I need Invoices in order to use Invoices.ReturnID as a criteria. This works perfectly. Except that there is no result for Category when there is no corresponding Invoices. Hence trying the LEFT JOIN to force all Category entries even when Sum(Amount) would be NULL on that line.

Best wishes,

Clive Astley