I am working on an Access database of Price and Discount information, I thought I had figured it out but just noticed that it actually is still excluding one record of my data from my query and I'm not entirely sure why. Here's what I have (I just made a small set for testing)

1st table is discount info, 2nd table is price info, 3rd table is my ideal results, 4th table is my current results

My SQL code running in Access:

SELECT PC.SKU, PC.Flavor, DC.STD, DC.END, DC.DID, DC.AMT, DC.QTY, PC.STD, PC.END, PC.FL, PC.FOB
FROM PC LEFT OUTER JOIN DC ON PC.SKU = DC.SKU
WHERE (([DC]![STD]>=[PC]![STD] And [DC]![STD]<=[PC]![END]) 
OR ([DC]![END]>=[PC]![STD] And [DC]![END]<=[PC]![END]) 
OR ([DC]![STD] IS NULL AND [DC]![END] IS NULL));

I'd like to see the price for the item even if it does not have discounts, and I'd like to see all the discounts even though it will duplicate the rows of some prices. I got this SQL syntax to work almost perfectly, but then noticed that there is no price for item 3 (Watermelon) from 5/1/22 - 5/11/22 in my current results, because there isn't a discount for that time. Because it is currently resulting the item 4, I'm not sure why it is excluding this one row. Anyone have any tips?

Simply, I'd like to see another row if there is a single day overlapping in either date range. If one discount is starting on the same day as the price ends, I'd still like that result. I'm somewhat new to SQL, any tips would be appreciated.

CodePudding user response：

The problem is that DC.STD is never null for Watermelon because there are other rows that satisfy the join condition. This is not the case with Peach.

You can fix the problem by moving all the conditions involving the outer table to the ON clause

SELECT PC.SKU, PC.Flavor, DC.STD, DC.END, DC.DID, DC.AMT, DC.QTY, PC.STD, PC.END, PC.FL, PC.FOB
FROM
    PC
    LEFT OUTER JOIN DC
        ON
            (PC.SKU = DC.SKU AND
              (
                (DC.STD>=PC.STD And DC.STD<=PC.[END]) OR
                (DC.[END]>=PC.STD And DC.[END]<=PC.[END])
              )
            );

We can even remove the IS NULL parts, since this is handled by the LEFT OUT JOIN automatically.

Note that overlapping of date ranges must be tested with

DC.STD <= PC.[END] AND DC.[END] >= PC.STD

I.e., the start of DC must be before or equal to the end of PC and the end of DC must be after or equal to the start of PC. Because you could have DC.STD < PC.STD AND DC.[END] > PC.[END] and these dates would overlap but fall through your test.

PC    |----------|
DC  |---------------|

So, the correct query is

SELECT PC.SKU, PC.Flavor, DC.STD, DC.END, DC.DID, DC.AMT, DC.QTY, PC.STD, PC.END, PC.FL, PC.FOB
FROM
    PC
    LEFT OUTER JOIN DC
        ON
            (PC.SKU = DC.SKU AND
             DC.STD <= PC.[END] AND DC.[END] >= PC.STD);

The Access query designer sometimes messes up this ON clause. So, you should store this query in a safe place in addition to a stored query.