MySQL Forums :: General :: query returns unwanted duplicates


Advanced Search

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.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');

Options: ReplyQuote


Subject Written By Posted
query returns unwanted duplicates Aaron D 10/11/2010 05:00PM
Re: query returns unwanted duplicates laptop alias 10/11/2010 05:21PM
Re: query returns unwanted duplicates Rick James 10/11/2010 07:12PM


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.