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

Rob gravatar image

Rob
33 2 2 2

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

Oleg gravatar image

Oleg
15.9k 2 4 24

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

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x371

asked: Nov 04, 2010 at 01:22 PM

Seen: 655 times

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