Select data with conditional rules (count) from other table

Question

I had these structure in my DB :

Table coupon
coupon_id , code, max_used

Table coupon_history
coupon_history_id, coupon_id, used_date

Can I get coupon data where "coupon.max_used" no more than total history at "coupon_history"? I get stuck here. I need to get a coupon_code. Please help me.

Answer 1

Try using JOIN and IN like this:

SELECT cc.* FROM coupon cc
WHERE cc.coupon_id IN
  (
    SELECT c.coupon_id FROM Coupon c
    LEFT JOIN
    (  SELECT coupon_id, COUNT(coupon_id) maxUsed
         FROM coupon_history GROUP BY coupon_id
    ) ch 
    ON c.Coupon_id = ch.Coupon_id
    WHERE c.max_used > ch.maxUsed
       OR ch.maxUsed IS NULL
  );

Or you can simply use JOIN like this:

SELECT cc.* FROM coupon cc
JOIN 
  (
    SELECT c.coupon_id FROM Coupon c
    LEFT JOIN
    (  SELECT coupon_id, COUNT(coupon_id) maxUsed
         FROM coupon_history GROUP BY coupon_id
    ) ch 
    ON c.Coupon_id = ch.Coupon_id
    WHERE c.max_used > ch.maxUsed
       OR ch.maxUsed IS NULL
  ) c ON cc.coupon_id = c.coupon_id;

Have a look at this SQLFiddle demo

---

Edit: about your second requirement

Try this query:

SELECT cc.*,c.TotalUsed FROM coupon cc
JOIN 
  (
    SELECT c.coupon_id,TotalUsed FROM Coupon c
    LEFT JOIN
    (  SELECT coupon_id, COUNT(coupon_id) TotalUsed
         FROM coupon_history GROUP BY coupon_id
    ) ch 
    ON c.Coupon_id = ch.Coupon_id
    WHERE c.max_used > ch.TotalUsed
       OR ch.TotalUsed IS NULL
  ) c ON cc.coupon_id = c.coupon_id;

See this SQLFiddle