Thanks Jeff I will give this a try on Friday when I get back. I'll think about the other suggestion also right now I have them pretty blocked but this would add an additional trap. -- Dan Stein Digital Software Solutions 799 Evergreen Circle Telford PA 18969 Land: 215-799-0192 Mobile: 610-256-2843 Fax 413-410-9682 FMP, WiTango, EDI,SQL 2000 [EMAIL PROTECTED] www.dss-db.com
> From: Jeffrey Bohmer <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Wed, 2 Jul 2003 17:25:31 -0600 > To: [EMAIL PROTECTED] > Subject: Re: Witango-Talk: Find duplicates > > > You might consider putting a unique constraint on the combination of > course_id and staff_id immediately after you've cleaned up your data. > Then, any attempt to register for the same course more than once > would result in an error instead of bad data. (Less maintenance in > the long run.) > > To get all record_id values having duplicated course_id values for a > certain staff_id, try this: > > select record_id, course_id > from tablename > where course_id in ( > select course_id > from tablename > where staff_id = 'staff_id_value' > group by course_id > having count(course_id) > 1 > ); > > > - Jeff > > > >> One step closer but A. Can't always assume that and if I could I think there >> might be case where there are more than 3 duplicates. >> >> Might be I just have to bring back the list and order it by course_ID and >> let them chose to delete the ones the want. >> -- >> Dan Stein >> Digital Software Solutions >> 799 Evergreen Circle >> Telford PA 18969 >> Land: 215-799-0192 >> Mobile: 610-256-2843 >> Fax 413-410-9682 >> FMP, WiTango, EDI,SQL 2000 >> [EMAIL PROTECTED] >> www.dss-db.com >> >> >>> From: "Ben Johansen" <[EMAIL PROTECTED]> >>> Organization: Intelisoft, Inc. >>> Reply-To: [EMAIL PROTECTED] >>> Date: Wed, 2 Jul 2003 15:52:35 -0700 >>> To: <[EMAIL PROTECTED]> >>> Subject: RE: Witango-Talk: Find duplicates >>> >>> Hi Dan, >>> >>> Since aggregates are allowed on the left side of the group by with a >>> grouping reference you might try the following >>> >>>> select course_id, max(record_id) >>>> from tablename >>>> where staff_id = 'staff_id_value' >>>> group by course_id >>>> having count(course_id) > 1; >>> >>> This assumes that out of 2 entries the first one is the one you want to >>> keep. If you want to delete the first record_id of the entries then use >>> min(). >>> >>> Ben Johansen - http://www.pcforge.com >>> Authorized Witango Reseller http://www.pcforge.com/WitangoGoodies.htm >>> Authorized MDaemon Mail Server Reseller >>> http://www.pcforge.com/AltN.htm >>> >>> >>> -----Original Message----- >>> From: Dan Stein [mailto:[EMAIL PROTECTED] >>> Sent: Wednesday, July 02, 2003 3:40 PM >>> To: [EMAIL PROTECTED] >>> Subject: Re: Witango-Talk: Find duplicates >>> >>> Jeff, >>> Thanks SQL works fine it brings back a list of the records that are >>> duplicates but only if I don't include the record ID so there is no way >>> then >>> delete the duplicates. >>> >>> Here is the overall issue. >>> >>> Due to some missing traps and changes to the way courses were created it >>> is >>> possible for a person to use the back button on the browser and register >>> twice for a course. >>> >>> I have fixed this but for the current record set and if they find >>> another >>> way to break the check I need an admin function so they can remove >>> duplicate >>> records. >>> >>> I was hoping I could bring back a list of the course registrations for a >>> person and then from that list let the admin person decide what to >>> delete. >>> >>> I know I could bring back the whole list of courses and then filter it >>> in a >>> for loop so it only shows duplicates but it would be so much easier to >>> just >>> grab the duplicates in the DBMS or Search action and be able to just >>> display >>> the results. >>> >>> Dan >>> -- >>> Dan Stein >>> Digital Software Solutions >>> 799 Evergreen Circle >>> Telford PA 18969 >>> Land: 215-799-0192 >>> Mobile: 610-256-2843 >>> Fax 413-410-9682 >>> FMP, WiTango, EDI,SQL 2000 >>> [EMAIL PROTECTED] >>> www.dss-db.com >>> >>> >>>> From: Jeffrey Bohmer <[EMAIL PROTECTED]> >>>> Reply-To: [EMAIL PROTECTED] >>>> Date: Wed, 2 Jul 2003 14:32:10 -0600 >>>> To: [EMAIL PROTECTED] >>>> Subject: Re: Witango-Talk: Find duplicates >>>> >>>> >>>> Here's the SQL: >>>> >>>> select course_id >>>> from tablename >>>> where staff_id = 'staff_id_value' >>>> group by course_id >>>> having count(course_id) > 1; >>>> >>>> (Sorry, I'm not sure how that would look in a search action.) >>>> >>>> - Jeff >>>> >>>> >>>> >>>> >>>>> Looking for an easy way to bring back a list of duplicate course ID's >>> for a >>>>> given staff_ID in a table based on a search action along if possible >>>>> >>>>> SQL 2000 >>>>> -- >>>>> Dan Stein >>>>> Digital Software Solutions >>>>> 799 Evergreen Circle >>>>> Telford PA 18969 >>>>> Land: 215-799-0192 >>>>> Mobile: 610-256-2843 >>>>> Fax 413-410-9682 >>>>> FMP, WiTango, EDI,SQL 2000 >>>>> [EMAIL PROTECTED] >>>>> www.dss-db.com >>>>> >>>> -- >>>> >>>> Jeffrey Bohmer >>>> VisionLink, Inc. >>>> _________________________________ >>>> 303.402.0170 >>>> www.visionlink.org >>>> _________________________________ >>>> People. Tools. Change. Community. >>>> >>> ________________________________________________________________________ >>>> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >>>> >>> >>> ________________________________________________________________________ >>> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >>> >>> ________________________________________________________________________ >>> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf >>> >> >> ________________________________________________________________________ >> TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > -- > > Jeffrey Bohmer > VisionLink, Inc. > _________________________________ > 303.402.0170 > www.visionlink.org > _________________________________ > People. Tools. Change. Community. > ________________________________________________________________________ > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
