MySQL queries clients who have not purchased

Question

Background:

We are creating a promotion system to give away free products to registered customers. We are trying to create a database that is flexible enough to handle multiple products and distributions. The requirements are that products can be dropped based on first-order drops for qualified customers.

Example:

Apple wants to give 1,000 ipads in March. They want to give a maximum of 1 hour. They want to give it to customers who are in California or New York. They want to limit the number of free ipads that a client can get (limit 1 to 15 days).

Data structure:

• Products - 1 entry for a unique product. e.g. Apple iPad

• ProductGiveAways

  • ProductID: AppleIpad
  • Quantity: 1000
  • StartDate: 01/03/2014
  • End date 03/31/2014
  • CustomerState: California, NewYork
  • BuyLimitDays: 15

Problem:

Using the above structure, we can make a request to the table of our customers and find out which ones are suitable for promotion.

What I cannot understand is the best way:

• Request customers in California or New York (is this a good option for joining another table?)
• When a customer logs in to find out which free products are not available to him, how can I exclude the Apple iPad if the customer has already received this freebie?

In other words:

• Say amazon.com wants to show me DVDs that I haven't bought yet. How can I request this?

• The right approach to get a list of previously purchased products, and then a request with a NOT?

Answer 1

, , . , . , , , .

select customerid 
from customer
where customerid not in (
                          select recipientid
                          from givenaway
                          where ..... and ....
                         )

Answer 2

, , , :

• • ProductId - INTEGER (IDENTITY PRIMARY KEY)
  • ProductName - VARCHAR
• States
  • StateId - INTEGER (IDENTITY PRIMARY KEY)
  • StateName - VARCHAR
• • CustomerId - INTEGER (IDENTITY PRIMARY KEY)
  • StateId - INTEGER (FOREIGN KEY)
• • PromotionId - INTEGER (IDENTITY PRIMARY KEY)
  • ProductId - INTEGER (FOREIGN KEY)
  • - INTEGER
  • StartDate - DATETIME
  • - DATETIME
  • BuyLimitDays - INTEGER
• PromotionState
  • PromotionId - INTEGER (FOREIGN KEY)
  • StateId - INTEGER (FOREIGN KEY)

, :

- ( ?)

(PromotionState) , , , , :

WHERE  
    (SELECT COUNT * FROM PromotionState x WHERE x.PromotionId = p.PromotionId) = 0
    OR NOT(ps.PromotionId IS NULL)

GROUP BY HAVING, GROUP BY - HAVING COUNT * = 0 OR HAVING SUM CASE WHEN (Conditions met) THEN 1 ELSE 0 END = 0

, , , Apple iPad, ?

, amazon.com DVD, . ?

, GROUP BY HAVING, , "", COUNT, SUM

, Query NOT?

, , , sub- , , , .

Answer 3

-, CustomerState California,NewYork, First Normal Form.

, .

- 1 ...

- 1 StateId: ( | NewYork |...) ...

- 1 ...

ProductGiveAways. PurchaseLimitDays ...

ProductGiveAways_State ProductGiveAwaysId StateId ...

_ - 1 ...

-, , , :

// This is just an example, you have to change the 'California', 'New York' with their ids
SELECT * FROM Customer WHERE StateId IN ('California', 'New York')

, , :

// It not an accurate sql, just an example
SELECT Product.* 
FROM Product 
JOIN ProductGiveAways ON ProductId
JOIN ProductGiveAways_State ON ProductGiveAwaysId
WHERE ProductId NOT IN (
    SELECT ProductId FROM Customer_Product JOIN ProductGiveAways ON ProductId
    WHERE CustomerId = /* the customer id */
    AND ( TO_DAYS(now()) - TO_DAYS(PurchaseDate) ) < PurchaseLimitDays
) 
AND StateId = /* customer StateId */
AND StartDate < now() < End Date // Elligible ProductGiveAways