mysql join question - am i doing this right

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|     date_expiration| 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 select all expired coupons for a given user. Is this correct?

select *
from coupons
join generatedcoupons on user_id = 233 and coupon_id=coupons.id
where coupons.owner=34 and (curdate()>date(coupons.date_expiration)

You need to join on the owner and the coupon id. Then put the values that you want to filter by in the where clause.

select *
from coupons c
join generatedcoupons g on c.owner=g.owner and g.coupon_id=c.id
where g.user_id = 233 and c.owner=34 and (curdate()>date(c.expiration_date)

A few things:

1. Your SQL has an error since the name of the column is expiration_date not date_expiration.

2. If the owner of coupon_id 15 is always (by definition) owner 34 then you should not repeat the owner column in the generatedcoupons table. To do so is redundant and potentially creates a situation in which the two tables disagree in such a way that you have no way to figure out what the correct data is.

3. You correctly understand about the JOIN part of the query and the WHERE part of the query, but you misplace a filter condition (user_id = 123) in the JOIN part instead of the WHERE part.

4. You tagged your question nested-queries, but the question doesn't actually involve any nested queries, but rather a straightforward JOIN.