Re: Witango-Talk: Find duplicates

[Jeffrey Bohmer]

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