Modify SQL to render unique Rows (vs. Dup''s)

Question

Modify SQL to render unique Rows (vs. Dup''s)

Express12

SSCoach

Points: 18514
More actions
August 15, 2005 at 10:42 am

#363461

Using the following VIEW DDL, can someone alter to guarantee ONLY 1 row for each Person.ID displayed? (currently, I receive 680 duplicate PersonID's using SQL: select PersonID, count(PersonID) from vwPersonApplications group by PersonID having count PersonID) > 1 order by 2 desc)
CREATE VIEW dbo.vwPersonApplications
AS
SELECT person.ID AS PersonID, person.LastName, person.FirstName,
       application.ID AS ApplicationID,
       application.StatusID AS ApplicationStatusID,
       application.AdminCreatedApplication,
       application.AuditStatusID,
       application.DateOpened,
       application.DateSubmitted,
       application.DateAuditOpened,
       email.Address AS Email, address.City,
       address.State, address.CountryCode
FROM
   dbo.Persons person LEFT OUTER JOIN
    dbo.CW_Applications application     ON person.ID = application.PersonID LEFT OUTER JOIN
    dbo.CW_Certifications certification ON person.ID = certification.PersonID LEFT OUTER JOIN
    dbo.Emails email                    ON person.ID = email.PersonID AND person.PrefEmailEID = email.ID LEFT OUTER JOIN
    dbo.Addresses address               ON person.PrefMailAID = address.ID

BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

so this has to be a left outer join...you want to get person info regardless of whether they have an applicationID or not ?!?!

also - how are you using your CW_Certifications table ?!

**ASCII stupid question, get a stupid ANSI !!!**

Express12 SSCoach Points: 18514 More actions · Answer 2

Yes, I need all Persons w/ OR w/out an Application

Likewise, all Persons w/ OR w/out a Certification row

BT

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

does a "select distinct...." reduce the # of rows at all ?!

also - what are some of the other requirements - eg: if a person has more than one applicationID, which is the one row that should be returned...would it be based on the ApplicationStatusID or any of the dates etc.. or just a max(ApplicationID) would do?!?!

more details on the filter criteria would help!!

**ASCII stupid question, get a stupid ANSI !!!**

Dick Schroth Right there with Babe Points: 725 More actions · Answer 4

It just seems like your view can't logically return a single record per person when you are joining a table with one record per person with several tables that could potentially have multiple records per person. The whole problem is deciding which value from the tables with multiple records that you want to return. But you could do it by making the view a GROUP BY on person.id and making each of the other fields a MAX(fieldname).

Dick Schroth