Join Where Rows Don't Exist or Where Criteria Matches...?
- by Greg
I'm trying to write a query to tell me which orders have valid promocodes. Promocodes are only valid between certain dates and optionally certain packages.
I'm having trouble even explaining how this works (see psudo-ish code below) but basically if there are packages associated with a promocode then the order has to have one of those packages and be within a valid date range otherwise it just has to be in a valid date range.
The whole "if PrmoPackage rows exist" thing is really throwing me off and I feel like I should be able to do this without a whole bunch of Unions. (I'm not even sure if that would make it easier at this point...)
Anybody have any ideas for the query?
if `OrderPromoCode` = `PromoCode`
then if `OrderTimestamp` is between `PromoStartTimestamp` and `PromoEndTimestamp`
then if `PromoCode` has packages associated with it
//yes
then if `PackageID` is one of the specified packages
//yes
code is valid
//no
invalid
//no
code is valid
Order:
OrderID* | OrderTimestamp | PackageID | OrderPromoCode
1 | 1/2/11 | 1 | ABC
2 | 1/3/11 | 2 | ABC
3 | 3/2/11 | 2 | DEF
4 | 4/2/11 | 3 | GHI
Promo:
PromoCode* | PromoStartTimestamp* | PromoEndTimestamp*
ABC | 1/1/11 | 2/1/11
ABC | 3/1/11 | 4/1/11
DEF | 1/1/11 | 1/11/13
GHI | 1/1/11 | 1/11/13
PromoPackage:
PromoCode* | PromoStartTimestamp* | PromoEndTimestamp* | PackageID*
ABC | 1/1/11 | 2/1/11 | 1
ABC | 1/1/11 | 2/1/11 | 3
GHI | 1/1/11 | 1/11/13 | 1
Desired Result:
OrderID | IsPromoCodeValid
1 | 1
2 | 0
3 | 1
4 | 0