Hi, I have two tables a marketing table and checkout_log table joined with a Left join. I want to display all records between the two tables but if there is a duplicate entry it should only display the duplicate entry that is >= curdate() (below there are multiple pk=7 fk=7 entries that indicate dupicate entries)
SELECT mkt.pk,mkt.owner_1_last_name,chk.expiredate FROM marketing mkt LEFT JOIN checkout_log chk ON mkt.pk = chk.fk WHERE mkt.owner_1_last_name = 'waller' AND mkt.drop_date = '2009-11-13'
The result I get is:
pk fk owner_1_last_name expiredate
5 5 waller 2009-05-05
7 7 waller 2010-11-01
7 7 waller 2009-05-06
8 waller
Results I want should look like this where waller with an expiredate of 2009-05-06 would be eliminated.
pk fk owner_1_last_name expiredate
5 5 waller 2009-05-05
7 7 waller 2010-11-01
8 waller
If I use expiredate >= curdate() in my select query then I only get:
7 7 waller 2010-11-01
How do I change my query to get the proper results?
Thanks for the help!
create and insert statements below
create table `checkout_log` (
`PK` double ,
`FK` double ,
`BOWLASTNAME` varchar (270),
`EXPIREDATE` date
);
insert into `checkout_log` (`PK`, `FK`, `BOWLASTNAME`, `EXPIREDATE`) values('1','5','waller','2009-05-05');
insert into `checkout_log` (`PK`, `FK`, `BOWLASTNAME`, `EXPIREDATE`) values('2','7','waller','2010-11-01');
insert into `checkout_log` (`PK`, `FK`, `BOWLASTNAME`, `EXPIREDATE`) values('3','7','waller','2009-05-06');
create table `marketing` (
`PK` double ,
`DROP_DATE` date ,
`OWNER_1_LAST_NAME` varchar (450),
`OWNER_1_FIRST_NAME` varchar (450)
);
insert into `marketing` (`PK`, `DROP_DATE`, `OWNER_1_LAST_NAME`, `OWNER_1_FIRST_NAME`) values('5','2009-11-13','waller','james');
insert into `marketing` (`PK`, `DROP_DATE`, `OWNER_1_LAST_NAME`, `OWNER_1_FIRST_NAME`) values('7','2009-11-13','waller','fred');
insert into `marketing` (`PK`, `DROP_DATE`, `OWNER_1_LAST_NAME`, `OWNER_1_FIRST_NAME`) values('8','2009-11-13','waller','albert');