MySQL Forums
Forum List  »  General

query returns unwanted duplicates
Posted by: Aaron D
Date: October 11, 2010 05:00PM

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.owner_1_last_name,chk.expiredate FROM marketing mkt LEFT JOIN checkout_log chk ON = 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),
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');

Options: ReplyQuote

Written By
query returns unwanted duplicates
October 11, 2010 05:00PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.