Mysql nested query passing one value to nested query

Question

Hi I have a coupon system that uses one table (called "coupons") to store info about each type of available coupon that one could generate, and another table (generatedcoupons) to store info about each coupon generated. I'm having a hard time comparing info in each table.

The schemas:

table: coupons
+----+------+--------------------+---------------+
|  id| owner|     expiration_date| limit_per_user|
|  15|    34| 2011-09-18 00:00:00|              2|
+----+------+--------------------+---------------+

table: generatedcoupons
+----+----------+------+--------------------+------+--------+
|  id| coupon_id| owner|                date|  used| user_id|
|   1|        15|    34| 2011-09-17 00:00:00| false|     233|
+----+----------+------+--------------------+------+--------+

I'm trying to run queries to display coupon from the point of view of a user (i.e. all queries will have where user_id='$userid'. I can't figure out how to display all coupons where the limit_per_user has not been met... here's what I've got that doesn't work:

select * 
from coupons 
where owner=34 
and (count(SELECT * from generatedcoupons where user_id=233 and coupon_id=coupons.id)<limit_per_user)

eswald · Accepted Answer · 2011-09-21 20:25:47Z

1

For MySQL, joining is usually faster and more reliable than subqueries, but makes you think a bit harder. In this case, you need to limit based on the number of joined rows, which requires a post-grouping calculation; fortunately, that's precisely what the HAVING clause is for:

select coupons.*
from coupons
left join generatedcoupons on user_id = 233 and coupon_id = coupons.id
where coupons.owner = 34
group by coupons.id
having count(generatedcoupons.id) < limit_per_user

answered Sep 21, 2011 at 20:25

eswald

8,4145 gold badges31 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

arychj · Accepted Answer · 2011-09-21 20:28:42Z

1

select * 
from coupons as c left join
generatedcoupons as g on g.user_id = 233 and g.coupon_id = c.id
where c.owner=34
group by c.id
having count(g.id) < c.limit_per_user

answered Sep 21, 2011 at 20:28

arychj

7073 silver badges22 bronze badges

Collectives™ on Stack Overflow

Mysql nested query passing one value to nested query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related