h9QKB9Z0

1MySQL query - SELECT only the latest result of a specific row?
2SELECT *
3
4FROM `coupons` AS c 
5LEFT JOIN `partners` AS p ON p.`id` = c.`pid`
6LEFT JOIN `bills` AS b ON b.`pid` = c.`pid`   
7
8WHERE 
9(
10CURRENT_DATE() = DATE_ADD(c.`expires`, INTERVAL 1 MONTH) // add something here?
11OR
12CURRENT_DATE() = DATE_ADD(b.`date`, INTERVAL 1 MONTH) 
13)
14	
15drop table if exists coupons;
16  create table coupons (id int not null, pid int not null, expires date, primary key(id));
17  drop table if exists bills;
18  create table bills (id int not null, pid int not null, `date` date, primary key(id));
19  drop table if exists partners;
20  create table partners ( id int, name varchar(20), primary key(id));
21	
22insert into partners values (1,'One');
23  insert into coupons values (1,1,'2011-12-12'), (2,1,'2011-11-11');
24  insert into bills values (1,1,'2011-12-12'), (2,1,'2011-11-11');
25	
26select * from coupons c
27  left join partners p on c.pid = p.id
28  left join bills b on b.pid = c.pid
29	
30where
31  (
32  current_date() = date_add(c.expires, interval 1 month)
33  or
34  current_date() = date_add(b.`date`, interval 1 month)
35  )
36	
37select *
38  from coupons c
39  where
40  c.id in
41  (select distinct c2.id from coupons c2 
42    left join partners p2 on c2.pid=p2.id
43    left join bills b2 on b2.pid=c2.pid
44    where
45     current_date()=date_add(c2.expires, interval 1 month)
46     or
47     current_date()=date_add(b2.`date`, interval 1 month)
48  )