Greetings,
I'm fairly new to MySQL--so I'm not sure if what I'm asking is simple
or not.
I have a table that has "duplicate records", but not really: the dupes
are not necessarily exactly like the originals, it seems.
In any case, I have this table...
CREATE TABLE `dues` (
`id` int(11) NOT NULL auto_increment,
`userID` int(11) NOT NULL default '0',
`dueYear` varchar(50) NOT NULL default '',
`duePaid` varchar(50) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `Userid` (`userID`)
) TYPE=MyISAM;
Where...
dues.userID = users.id
And...
users.id can be in 0 or more records in the table "dues".
Okay, by way of example -- this statement...
SELECT id, userID, dueYear, duePaid
FROM dues
WHERE userID = 218
ORDER BY dueYear
...yeilds:
id userID dueYear duePaid
2158 218 1995 Non-Member
2159 218 1996 Non-Member
2160 218 1997 Non-Member
2161 218 1998 Non-Member
2162 218 1999 Non-Member
2163 218 2000 Non-Member
2164 218 2001 Yes
2165 218 2002 Non-Member
2078 218 2003 Yes
2166 218 2003 Non-Member
2167 218 2004 Non-Member
2168 218 2005 Non-Member
You can see that I have a "duplicate year" (2003) for this user. I have
lots of this in my dues table, but not for every user--it seems pretty
random.
(how they got there is still to be tracked down, but is not a huge
concern)
What I want to know is how do I find these "double year" records?
I want to be able to find all such instances of "doubling up on the
year" for every `userID` easily--and then eventually to delete the
"extra" one (either one, no matter).
(for every "users.id", that is)
(I need to be able to save a list of which userIDs where "infected",
too so we can go back and make sure the dues information is correct.
I could write a web script to do this easily enough, but I really want
to be able to do it in SQL statements.
I have tried and tried to figure it out on my own, but have failed.
Can anyone offer suggestions?
- me
======================================================================
"In 5 months all Web Pages will be created with Lasso...
In 5 minutes you'll find out why...
In 5 days you'll wonder how your website ever existed without Lasso...
In 5 weeks you'll get a raise...
Download Lasso Today!"
- blueworld.com | December 10 1997
http://web.archive.org/web/19971210093857/http://www.blueworld.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
