x

query to return duplicates

I have a table (tblApplications) that joins to a table (tblColleges)...one Application can have many Colleges. I would like to get all idsAppID that has more that one entry in the tblColleges table. Once this is accomplished, I would also like to narrow the result set further by only returning Applications by "Term" (39 or 40) and "Status" (8)

Example

--tblApplicaiton

 idsAppID, Name, idsTerm, idsStatus
 123, John Smith, 39 or 40, 8

--tblCollege

 idsAppID, Name
 123, Apple College
 123, Orange College
 123, Grape College

Any advice would be appreciated.

more ▼

asked Nov 04, 2010 at 01:22 PM in Default

avatar image

Rob
33 2 2 4

Rob, my apologies if this isnt, but I am wondering if this is a college/homework question? If so then it's no problem, we are very happy to help but it will assist us in how we help you if we know that it is. Please edit the question and add the homework tag if it is. Thanks

Nov 04, 2010 at 01:31 PM Fatherjack ♦♦

no homework - just attempting to get a resultset for an export to pass to a department.

Nov 04, 2010 at 01:54 PM Rob
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This should do it:

 select 
     app.idsAppID, app.[Name], app.idsTerm, app.idsStatus
     from tblApplication app inner join
     (
         select idsAppID
             from tblCollege
             group by idsAppID
             having count(1) > 1
     ) college
         on app.idsAppID = college.idsAppID
     where 
         app.idsTerm in (39, 40)
         and app.idsStatus = 8;

Oleg

more ▼

answered Nov 04, 2010 at 02:00 PM

avatar image

Oleg
17.2k 3 7 28

Oleg, this is exactly what I was looking for...thank you very much. Rob

Nov 04, 2010 at 05:02 PM Rob

@Rob: If @Oleg answer helped you and/or resolved the issue please mark it as an answer..thanks.

Nov 05, 2010 at 10:42 AM DaniSQL
(comments are locked)
10|1200 characters needed characters left

Or you can use a common table expression, if you do that it would be easy if you like to delete all the duplicates. (sometimes I hate iPhone)

 ;with college as
 (
 select idsAppID, row_number() over(partition by idsAppID order by somecolumn) as rownum
             from tblCollege
             group by idsAppID
             having count(1) > 1
  )
 Select 
     app.idsAppID, app.[Name], app.idsTerm, app.idsStatus
     from tblApplication app inner join college
         on app.idsAppID = college.idsAppID
     where 
         app.idsTerm in (39, 40)
         and app.idsStatus = 8;

if you want to delete the duplicates you should replace the select with : DELETE FROM college WHERE rownum>1

more ▼

answered Nov 07, 2010 at 08:53 AM

avatar image

Håkan Winther
16.5k 37 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x428

asked: Nov 04, 2010 at 01:22 PM

Seen: 795 times

Last Updated: Nov 04, 2010 at 01:30 PM

Copyright 2016 Redgate Software. Privacy Policy