I am using MSSQL7 at work along with VB6, and while I have the VB work sorted, I am struggling with the SQL quite badly.
Basically, I will try to explain my problem as best as possible, and will go from there.
Firstly, here is my SQL statement as it stands so far
SELECT a.AuthCode, a.ReportTitle, a.DateCreated, count(b.AuthCode) as SKUs FROM tblRtns_Header a INNER JOIN tblRtns_Detail b ON b.AuthCode = a.AuthCode INNER JOIN (SELECT a1.ITEM_ID, a1.TOTAL_QTY FROM ITEM_QUANTITY a1) g ON b.SKU_ShortCode = g.ITEM_ID AND g.TOTAL_QTY > b.QtyToKeep GROUP BY a.AuthCode, a.ReportTitle, a.DateCreated ORDER BY a.DateCreated
I have 3 tables. The idea is that the external company requests a certain amount of individual items, and this is sent to the local system. The local system will check the current stock on hand level and if there is enough stock to satisfy what they are after, it will display the item as needing to be returned.
This system is displayed over 2x webpages in that the first page shows a brief outline (Authorisation Number (AuthCode), Report Title (ReportTitle), Date of Report (DateCreated) and the total items which have stock to be returned (SKUs).
This first page works with the SQL code showing above, in that it shows the number of items which have stock to be returned, but only if there is at least one item which has stock. If a report only has items that have NO stock to be returned, then that report header is not displayed at all. This is what is wrong - all headers need to be displayed whether they have items to be returned inside or not.
With the code above, I am getting the following results:
Auth Code | Title ----------- | Date ------- | Total SKUs | 00101 ---- | Returns Report | 11/11/2007 | 3 00103 ---- | Returns Report | 11/11/2007 | 2
Now I should be getting this result:
Auth Code | Title ----------- | Date ------- | Total SKUs | 00101 ---- | Returns Report | 11/11/2007 | 3 00102 ---- | Returns Report | 18/11/2007 | 0 00103 ---- | Returns Report | 11/11/2007 | 2
Please note I have included the hyphen/dash marks to help with formatting and clear display on the forum only :)
I will answer any questions as best as I can. If anyone could point me in the right direction, it would be HUGELY appreciated as with deadlines at work, I am up against it, and am struggling with this piece of work :(
Thanks in advance to anyone who can help,