父子关系，不使用PHP获取并重新使用返回值[关闭]

Question

I'm trying to make a relation between two tables. I can do what I want with fetching the data with PHP and using returning value again in a query. But as far as I researched, it doesn't seem an efficient method.

How can I get the winning coupons when I'm finalizing the bet #3 as YES with a SQL query?

(It should return 3 and 4)

Coupon table,

ID coupon_id bets bets_played  played_by
0    2        2       yes         JOHN
1    2        3       no          JOHN
2    3        1       yes         JANE
3    3        3       yes         JANE
4    4        3       yes         SARAH
5    4        2       no          SARAH

Bets table,

ID  result
1    yes
2    no
3    NULL

Thank you.

Answer 1

select distinct c.*
from coupon c
join bets b on b.result = c.bets_played and b.result = 'yes'