asked on

Select specific duplicate row based on specific criteria

I am not trained in SQL. I'm trained in other programming languages, so explain this slowly for me.

I've been beating my head against the wall with this query. I spent a couple of days on it and got the results that I wanted, but noticed today that I had duplicates rows. Essentially, it's a list of clergy members. The clergy members have multiple addresses. What I'm most concerned about is the address types 'Main\Home Address' and the 'Parsonage Address'. I was able to figure out a join statement that gets me only these two address types, but I didn't plan on some of the clergy having both, so they both show up causing my duplicates.

Essentially, if both address types are listed for a clergy member, then I only want 'Main\Home Address to appear'. Otherwise, I want those clergy that have 'Main\Home Address' and those clergy members that have 'Parsonage Address' to appear in the query. The code is below. It's an awful database, so I'm hoping that what I've typed makes sense. Again, I'm not trained in SQL, but I've tried to comment within the code so things are clear. I really hope for some ideas on what to do. Thanks for the help.

SELECT DISTINCT
OtherPersonalInformation_GCFA203.int_value AS Id,
P.first_name AS FirstName,
P.last_name AS LastName,
P.nick_name AS PreferredName,
P.middle_name AS MiddleName,
ISNULL(tb_person_suffix.lookup_value, '') AS person_suffix,
ISNULL(tb_person_title.lookup_value, '') AS person_title,

dbo.core_funct_primary_email(P.person_id) AS Email,

ISNULL(CELLPHONE.list_cell_phone, '') AS Phone2,
ISNULL(BUSINESSPHONE.list_business_phone, '') AS Phone1,
ISNULL(HOMEPHONE.list_home_phone, '') AS Phone3,

tb_AddType.lookup_value as Addtype1,

ISNULL(A.street_address_1,'') AS MailAddress1,
ISNULL(A.city,'') AS MailCity,
ISNULL(A.state, '') AS MailState,
ISNULL(A.postal_code,'') AS MailZip,
ISNULL(A.country,'') AS MailCountry,

Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) AS ClergyStatus,

CASE WHEN CONVERT(VARCHAR(10),ISNULL(ConferenceRelationship_Whendidthecurrentrelationshipbegin220.datetime_value,0),101) = '01/01/1900' -- to account for accidental time
THEN ''
ELSE CONVERT(VARCHAR(10), ConferenceRelationship_Whendidthecurrentrelationshipbegin220.datetime_value, 101)
END AS StatusDate,

CASE

            WHEN ISNULL(ClergyLaity_Clergy91.attribute_id,'') = 91
                  THEN 'Clergy'

            WHEN ISNULL(ClergyLaity_Laity107.attribute_id,'') = 107
                  THEN 'Laity'

            WHEN ISNULL(ClergyLaity_DSACLM174.attribute_id,'') = 174
                  THEN 'Laity'

            END AS ClergyOrLay,

CASE
            WHEN P.record_status = 0 AND Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RA'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RD'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RE'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RP'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'DR'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RL'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'OR'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'ROA'
                                                      OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'RO'
                  THEN 'Retired'
            WHEN P.record_status = 0
                  THEN 'Active'
            WHEN P.record_status = 1 AND tb_inactive_reason.lookup_value = 'Deceased'
                  THEN 'Deceased'
            WHEN P.record_status = 1 AND Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'AL'
                                                            OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'DI'
                                                            or Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'HL'
                                                            OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'HR'
                                                            OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'SU'
                                                            OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'TO'
                                                            OR Left(CLConferenceRelationship_CurrentConferenceRelationship192.lookup_value,2) = 'WI'
                                                            OR tb_inactive_reason.lookup_value = 'Left denomination'
                                                            OR tb_inactive_reason.lookup_value = 'moved'
                  THEN 'Inactive'
            WHEN P.record_status = 2
                  THEN 'Pending'
            WHEN P.record_status = 3
                  THEN 'Undefined'
            END AS AccountStatus,

CASE WHEN CONVERT(VARCHAR(10),ISNULL(ConferenceRelationship_Whendidthecurrentrelationshipbegin220.datetime_value,0),101) = '01/01/1900' -- to account for accidental time
THEN ''
ELSE CONVERT(VARCHAR(10), ConferenceRelationship_Whendidthecurrentrelationshipbegin220.datetime_value, 101)
END AS AccountStatusDate,

OtherPersonalInformation_GCFA203.int_value AS GCFAId,

ISNULL(ConferenceRelationship_GBOPHBID92.varchar_value,0) as PensionParticipationNumber,

CASE P.gender
WHEN 0 THEN 'Male'
WHEN 1 THEN 'Female'
WHEN 2 THEN 'Unknown'
WHEN -1 THEN 'Undefined'
END AS Gender,

ISNULL(CLOtherPersonalInformation_RaceEthnicity183.lookup_value,0) AS Ethnicity,

CASE WHEN CONVERT(VARCHAR(10),ISNULL(P.birth_date,0),101) = '01/01/1900' -- to account for accidental time
THEN ''
ELSE CONVERT(VARCHAR(10), P.birth_date, 101)
END AS DateOfBirth,

tb_marital_status.lookup_value as MaritalStatus

FROM core_person P
LEFT OUTER JOIN core_v_phone_business BUSINESSPHONE ON businessphone.person_id = P.person_id
LEFT OUTER JOIN core_v_phone_cell CELLPHONE ON cellphone.person_id = P.person_id
LEFT OUTER JOIN core_person_attribute OtherPersonalInformation_Donotsynch206 ON OtherPersonalInformation_Donotsynch206.person_id = P.person_id
      AND OtherPersonalInformation_Donotsynch206.attribute_id = 206
LEFT OUTER JOIN core_person_attribute ConferenceRelationship_CurrentConferenceRelationship192 ON ConferenceRelationship_CurrentConferenceRelationship192.person_id = P.person_id
      AND ConferenceRelationship_CurrentConferenceRelationship192.attribute_id = 192
LEFT OUTER JOIN core_lookup CLConferenceRelationship_CurrentConferenceRelationship192 ON ConferenceRelationship_CurrentConferenceRelationship192.int_value = CLConferenceRelationship_CurrentConferenceRelationship192.lookup_id
      AND CLConferenceRelationship_CurrentConferenceRelationship192.organization_id = 1
LEFT OUTER JOIN core_person_attribute OtherPersonalInformation_GCFA203 ON OtherPersonalInformation_GCFA203.person_id = P.person_id
      AND OtherPersonalInformation_GCFA203.attribute_id = 203
LEFT OUTER JOIN core_person_address PA ON PA.person_id = P.person_id AND PA.address_type_luid IN (10336,10403) --Display address types 'Main/Home Address (10403) and 'Parsonage Address' (10336)
LEFT OUTER JOIN core_address A ON A.address_id = PA.address_id
LEFT OUTER JOIN core_area AREA ON AREA.area_id = A.area_id
LEFT OUTER JOIN core_family_member FM ON FM.person_id = P.person_id
LEFT OUTER JOIN core_v_phone_home HOMEPHONE ON homephone.person_id = P.person_id
LEFT OUTER JOIN core_lookup tb_person_title ON tb_person_title.lookup_id = P.title_luid AND tb_person_title.organization_id = 1
LEFT OUTER JOIN core_lookup tb_person_suffix ON tb_person_suffix.lookup_id = P.suffix_luid AND tb_person_suffix.organization_id = 1
LEFT OUTER JOIN core_lookup tb_family_role ON tb_family_role.lookup_id = FM.role_luid AND tb_family_role.organization_id = 1
LEFT OUTER JOIN core_lookup tb_inactive_reason ON tb_inactive_reason.lookup_id = P.inactive_reason_luid
      AND tb_inactive_reason.organization_id = 1
LEFT OUTER JOIN core_lookup tb_Addtype ON tb_Addtype.lookup_id = PA.address_type_luid AND tb_Addtype.organization_id = 1
LEFT OUTER JOIN core_person_attribute ClergyLaity_DSACLM174 ON ClergyLaity_DSACLM174.person_id = P.person_id AND ClergyLaity_DSACLM174.attribute_id = 174
LEFT OUTER JOIN core_person_attribute ClergyLaity_Clergy91 ON ClergyLaity_Clergy91.person_id = P.person_id AND ClergyLaity_Clergy91.attribute_id = 91
LEFT OUTER JOIN core_person_attribute ClergyLaity_Laity107 ON ClergyLaity_Laity107.person_id = P.person_id AND ClergyLaity_Laity107.attribute_id = 107
LEFT OUTER JOIN core_person_attribute ConferenceRelationship_Whendidthecurrentrelationshipbegin220 ON ConferenceRelationship_Whendidthecurrentrelationshipbegin220.person_id = P.person_id AND ConferenceRelationship_Whendidthecurrentrelationshipbegin220.attribute_id = 220
LEFT OUTER JOIN core_person_attribute ConferenceRelationship_GBOPHBID92 ON ConferenceRelationship_GBOPHBID92.person_id = P.person_id AND ConferenceRelationship_GBOPHBID92.attribute_id = 92
LEFT OUTER JOIN core_person_attribute OtherPersonalInformation_RaceEthnicity183 ON OtherPersonalInformation_RaceEthnicity183.person_id = P.person_id AND OtherPersonalInformation_RaceEthnicity183.attribute_id = 183
LEFT OUTER JOIN core_lookup CLOtherPersonalInformation_RaceEthnicity183 ON OtherPersonalInformation_RaceEthnicity183.int_value = CLOtherPersonalInformation_RaceEthnicity183.lookup_id AND CLOtherPersonalInformation_RaceEthnicity183.organization_id = 1
LEFT OUTER JOIN core_lookup tb_marital_status ON tb_marital_status.lookup_id = P.marital_status AND tb_marital_status.organization_id = 1
--LEFT OUTER JOIN core_lookup tb_Addtype ON tb_Addtype.[lookup_id] = PA.[address_type_luid] AND tb_Addtype.organization_id = 1

WHERE
(P.organization_id = 1)
AND ( P.record_status IN (0,1) )
AND (P.member_status IN (
                                          12576, --Clergy
                                          12577, --Clergy Couple
                                          12580, --DSA/CLM
                                          12585 --Retired Clergy
                                          ))
AND (ISNULL(OtherPersonalInformation_Donotsynch206.int_value, 0) = 0)
AND (ConferenceRelationship_CurrentConferenceRelationship192.int_value IN (
                                                                                                                  --Active
                                                                                                                  12502, --AF Affiliate member
                                                                                                                  12503, --AM Associate member
                                                                                                                  12504, --BH Bishop
                                                                                                                  12591, --DC Deaconess
                                                                                                                  12508, --DM Diaconal Minister
                                                                                                                  12509, --DP Deacon recognized from other denomination servi
                                                                                                                  12511, --SY District Superintendent Assignment (Supply) - DSA
                                                                                                                  12513, --FD Deacon in full connection
                                                                                                                  12514, --FE Elder in full connection
                                                                                                                  12515, --FL Full time Local Pastor
                                                                                                                  12522, --LM Certified Lay Minister
                                                                                                                  12525, --OA Associate Member of other Annual Conference
                                                                                                                  12526, --OD Deacon member of other annual conference
                                                                                                                  12527, --OE Elder mem of other conf or Methodist denom
                                                                                                                  12528, --OF Full member of other denomination
                                                                                                                  12529, --OP Provisional member of other annual conference
                                                                                                                  12531, --PD Provisional Deacon
                                                                                                                  12532, --PE Provisional Elder
                                                                                                                  12533, --PL Part time Local Pastor
                                                                                                                  12553, --SP Student Local Pastor
                                                                                                                  12557, --TI Transfer Membership In
                                                                                                                  ----Deceased
                                                                                                                  12506, --DE Deceased
                                                                                                                  ----Retired
                                                                                                                  12530, --OR OR Retired member of other annual conference
                                                                                                                  12547, --ROA Retired Other Conference Associate Member
                                                                                                                  12546, --RO Retired from Other Annual Conference, serving a
                                                                                                                  12510, --DR Retired Diaconal Minister
                                                                                                                  12535, --RA Retired Associate Member
                                                                                                                  12537, --RD Retired Deacon in Full Connection
                                                                                                                  12539, --RE Retired full member
                                                                                                                  12550, --RP Retired Provisional Member
                                                                                                                  12543, --RL Retired Local Pastor
                                                                                                                  --Inactive
                                                                                                                  12583, --AL Administrative Location
                                                                                                                  12507, --DI Discontinued
                                                                                                                  12518, --HL Honorable Location
                                                                                                                  12520, --HR Honorable Location Retired
                                                                                                                  12554, --SU Surrendered Credentials
                                                                                                                  12558, --TO Transfer Membership Out
                                                                                                                  12561 --WI Withdrawn
                                                                                                                  ))

ORDER BY AccountStatus ASC, LastName ASC, FirstName ASC

arnold

You seem to have an assortment, IMHO, by posting your queries without the context of your data organization/schema (table creation)
and sample data,
From your assertion, the address is not part of the individual's record, so using the referential addresstype in the group by rule will distinguish
Persona addresstype1
From
Persona addresstype2
As two separate distinct records.

....

digitap

ASKER

This is where my lack of sql knowledge really shines. I'm not sure how much someone needs to know to help me. Can I share the table structure without breaking confidentiality?

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

digitap

ASKER

In the end, I wasn't allowed to post more than what I did. The vendor, who'd not been helping, finally reported back some join statements that allowed me to come up with a solution. It's pretty much what arnold had suggested, so I'm giving him the points.