SQL Question

[DGULLIVER]

Hi Everyone,

Been a few years since I had to post anything here so I'm glad to be back.

I've been asked to do a report to show vouchers that have been issued and presented for July (this year and last year).
I have done this SQL:

SELECT tblOutlet.OL_NAME, Format([VI_TRANSACTION_DATE_TIME],"mmmm yyyy") AS MONTH_NAME, Count(tmpVoucherIssues.VI_ID) AS ISSUED, Sum(IIf(Nz([VH_ID])>0,1,0)) AS PRESENTED
FROM (tmpVoucherIssues LEFT JOIN tblVoucherHeader ON tmpVoucherIssues.VI_FORM_NUMBER = tblVoucherHeader.VH_NUMBER) INNER JOIN tblOutlet ON tmpVoucherIssues.VI_OL_ID = tblOutlet.OL_ID
GROUP BY tblOutlet.OL_NAME, Format([VI_TRANSACTION_DATE_TIME],"mmmm yyyy");

Returned Data:

Retailer 1  July 2012   269 202
Retailer 1  July 2013   285 173
Retailer 2  July 2012   170 112
Retailer 2  July 2013   204 124
Retailer 3  July 2012   201 151
Retailer 3  July 2013   228 143
Retailer 4  July 2013   584 372
Retailer 5  July 2013   949 565
Retailer 6  July 2012   132 86
Retailer 6  July 2013   221 141

In July 2012 Reteiler 4 and 5 did not produce any results, but I want the results to show as:

Retailer 4  July 2012   0   0
Retailer 5  July 2012   0   0

Please can you help me to re-write the sql?

Thanks in advance.
Darren

Please note I'm using mySQL as my database.

[Klahr_R]

I can't write your query but I think I see the logic problem.

Will the retailer that has no activity exist in the tmpVoucherIssues table?
You need to use a table where all retailers are present.
This looks like a prety good review tool.

[DGULLIVER]

Thanks for reply - I have 3 tables in my Query. The retailer will exsit in tblOutlet. I'm missing something but I just cannot see it.

[pritaeas]

No results because the LEFT JOIN finds no matches? If so, you may want to start with both months and join everything based on that.

[BitBlt]

You may want to re-think the order that the tables appear in the join chain. You ALWAYS want to start with the table that will have the interesting values. In this case, tblOutlet has all the retailers, right? So, put that first, then LEFT JOIN tblVoucherIssues LEFT JOIN tblVoucherHeader. Then, be sure to use the ISNULL function on the things you're counting or summing to use a 0 (zero) instead of null so your aggregations don't get messed up.

It might look something like this (although this hasn't been tested...sorry didn't have time!)

SELECT tblOutlet.OL_NAME, 
Format([VI_TRANSACTION_DATE_TIME],"mmmm yyyy") AS MONTH_NAME, 
Count(isnull(tmpVoucherIssues.VI_ID, 0)) AS ISSUED, 
Sum(IIf(Nz(isnull([VH_ID], 0))>0,1,0)) AS PRESENTED
FROM tblOutlet 
LEFT JOIN tmpVoucherIssues ON tmpVoucherIssues.VI_OL_ID = tblOutlet.OL_ID
LEFT JOIN tblVoucherHeader ON tmpVoucherIssues.VI_FORM_NUMBER = tblVoucherHeader.VH_NUMBER
GROUP BY tblOutlet.OL_NAME, Format([VI_TRANSACTION_DATE_TIME],"mmmm yyyy");

Hope this helps!