Hi all

After a bit of struggling to get my thoughts correct I have the following query

<pre>SELECT mItems.*
, (SELECT SUM(sSaleInvoiceItem.bQty)
FROM sSaleInvoiceItem
JOIN tSaleInvoice
ON tSaleInvoice.iID = sSaleInvoiceItem.iPID
JOIN mAccounts
ON mAccounts.iID = tSaleInvoice.iRepresentativeID
WHERE CAST(tSaleInvoice.tDt AS DATE) BETWEEN '2010.1.1' AND '2010.1.31'
AND sSaleInvoiceItem.iItemID = mItems.iID) AS bQty
FROM mItems
WHERE mItems.cTypeID = 'FIN'
AND (mItems.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = mItems.iID))
AND mItems.cTypeID IN ('FIN')
ORDER BY UPPER(mItems.cDesc), UPPER(mItems.cPack)</pre>

The above returns a dataset only for one representative. To take it a step further I tried the following for the main query
<pre> FROM mItems
LEFT JOIN sSaleInvoiceItem
ON sSaleInvoiceItem.iItemID = mItems.iID</pre>

It stops returning the required NULL bQty and just returns 3 records, ie. records with items that were sold.

How do I get grouped dataset for each representative?

Please advise.

Thanks and regards
Bhavbhuti