November 29, 2011 at 3:02 am
Hi,
I've been given the task of coming up with a query to allow our company send out emails to our customers based on their geographical location and order history,
Example Scenarios would be:
Email all Customers from State 'A' who have never ordered Product 'A'
Email all Customers from States 'A', 'B','C' who have ordered Product 'A' but not Product 'B'
Email all Customers except Customers in Suburb 'K' who have orderd Product Group 'C'
Email all Customer in States 'A', 'B', 'C', 'D' who have ordered 'Product 'A' but not not ordered it in the past 3 months
etc.....
Customers have Suburb and State attibutes and Products have Product Group attribute.
I have created 3 tables - one to hold the Email entity and 2 further tables to hold the Product criteria and one to hold Geograpical criteria that the user will select from the front end - one row per criteria set - along with a Boolean flag column to toggle on whether this criteria is to be included or excluded.
An example row in the Product Criteria table might be:
EMAIL TABLE
Email_ID Email_Name
1 ChocolateEmail
EMAIL PRODUCT TABLE
Email_ID Product Group Product On_Order
1 Chocolate Mars Bar TRUE
EMAIL REGION TABLE
Email_ID State Suburb Include
1 NY NULL TRUE
This would equate to a request to send the Chocolate Email to all Customers who have past order records containing Mars Bars and located in ALL Suburbs in NY State
I'm trying to return the list of Customer records in as efficient way as possible.
The main tables involved are Customer, Order, Order Items, Products so I would join these to my tables above to get back the list of Customer Codes
What I am struggling with is how to cater for all possibilites wihout writing a massive complex query.
From googling today it looks like using LEFT OUTER JOIN to join all the tables and then maybe a CASE statement in the WHERE clause to decide whether to use IS NULL or IS NOT NULL based on whether the exclude boolean flag it set or not.
Or perhaps just using NOT IN or NOT EXISTS and subqueries to filter the data set.
Seems to be plenty of ways to try and tackle this one so just looking for any suggestions from perhaps anyone else who may have done the same thing.
Hope I explained it well enough!
November 29, 2011 at 5:42 am
JayK (11/29/2011)
Hi,I've been given the task of coming up with a query to allow our company send out emails to our customers based on their geographical location and order history,
Example Scenarios would be:
Email all Customers from State 'A' who have never ordered Product 'A'
Email all Customers from States 'A', 'B','C' who have ordered Product 'A' but not Product 'B'
Email all Customers except Customers in Suburb 'K' who have orderd Product Group 'C'
Email all Customer in States 'A', 'B', 'C', 'D' who have ordered 'Product 'A' but not not ordered it in the past 3 months
etc.....
Customers have Suburb and State attibutes and Products have Product Group attribute.
I have created 3 tables - one to hold the Email entity and 2 further tables to hold the Product criteria and one to hold Geograpical criteria that the user will select from the front end - one row per criteria set - along with a Boolean flag column to toggle on whether this criteria is to be included or excluded.
An example row in the Product Criteria table might be:
EMAIL TABLE
Email_ID Email_Name
1 ChocolateEmail
EMAIL PRODUCT TABLE
Email_ID Product Group Product On_Order
1 Chocolate Mars Bar TRUE
EMAIL REGION TABLE
Email_ID State Suburb Include
1 NY NULL TRUE
This would equate to a request to send the Chocolate Email to all Customers who have past order records containing Mars Bars and located in ALL Suburbs in NY State
I'm trying to return the list of Customer records in as efficient way as possible.
The main tables involved are Customer, Order, Order Items, Products so I would join these to my tables above to get back the list of Customer Codes
What I am struggling with is how to cater for all possibilites wihout writing a massive complex query.
From googling today it looks like using LEFT OUTER JOIN to join all the tables and then maybe a CASE statement in the WHERE clause to decide whether to use IS NULL or IS NOT NULL based on whether the exclude boolean flag it set or not.
Or perhaps just using NOT IN or NOT EXISTS and subqueries to filter the data set.
Seems to be plenty of ways to try and tackle this one so just looking for any suggestions from perhaps anyone else who may have done the same thing.
Hope I explained it well enough!
Hello and welcome to SSC!
Your issue seems fairly interesting, similar to SQL Puzzles posted across the web. I'd love to be able to help!
Unfortunately, it seems that your readily consumable sample data and DDL scripts have fallen off your post. Or perhaps you were unaware of the benefits of providing them? When you have time, please read this article[/url] about the best way to provide us with the necessary scripts to allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply