Member Avatar for jonc

Hi,

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,
Jon

  • 1 Contributor
  • 1 Reply
  • 93 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by jonc
Member Avatar for jonc

Hi, after battering my head over this problem some more, I have figured it out and it all appears to be working as expected :)

It is all down to my joins which I had a feeling was the case all along!

The SQL which works is as follows:

SELECT a.AuthCode, a.ReportTitle, a.DateCreated, count(b.AuthCode) as SKUs
FROM tblRtns_Detail b LEFT OUTER JOIN
(SELECT a1.ITEM_ID, a1.TOTAL_QTY FROM ITEM_QUANTITY a1) g ON
	b.SKU_ShortCode = g.ITEM_ID RIGHT OUTER JOIN
tblRtns_Header a ON
	b.AuthCode = a.AuthCode AND
	g.TOTAL_QTY > b.QtyToKeep

GROUP BY a.AuthCode, a.ReportTitle, a.DateCreated
ORDER BY a.DateCreated

(just incase someone has a similar problem in the future!)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.