query to return duplicates

Question

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.

Answer 1

0

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

answered Nov 04 '10 at 02:00 PM

Oleg
15.4k ● 1 ● 4 ● 24

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

Nov 04 '10 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 '10 at 10:42 AM DaniSQL

(comments are locked)

Answer 2

0

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

answered Nov 07 '10 at 08:53 AM

Håkan Winther
15k ● 29 ● 35 ● 46

(comments are locked)

query to return duplicates

Follow this question

Related Questions