question

muk avatar image
muk asked

need to change one of the joins

Hello everyone, I have this query where it gets all people from the database with a preferred address. I need it to get everyone from the person table whether they have a preferred address or not and if they do, to list this address. I suspect it is something with my joins. Any input is appreciated. Select All PERSON.ID, PERSON.PREFIX As Prefix, PERSON.FIRST_NAME As 'First Name', PERSON.MIDDLE_NAME As 'Middle Name', PERSON.LAST_NAME As 'Last Name', Max(Case When (PERSON.PERSON_CORP_INDICATOR Is Null And PSEASON.ADDR_TYPE = 'B') Then PERSON1.PREFERRED_NAME Else Null End) As 'Company Name', Max(Case When ADDRESS_LS.POS = 1 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 1', Max(Case When ADDRESS_LS.POS = 2 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 2', Max(Case When ADDRESS_LS.POS = 3 Then ADDRESS_LS.ADDRESS_LINES Else '' End) As 'Address Line 3', ADDRESS.CITY As City, ADDRESS.STATE As State, ADDRESS.ZIP As Zip, PSEASON.ADDR_TYPE As 'Address Type', PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES As [Preferred Email], Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'CELL') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As 'Cell', Max(Case When (PERPHONE.POS = 1 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER Else (Case When (PERPHONE.POS = 2 And PERPHONE.PERSONAL_PHONE_TYPE = 'HOM') Then PERPHONE.PERSONAL_PHONE_NUMBER End) End) As 'Home', Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONES Else ADR_PHONES1.ADDRESS_PHONES End) As 'Business Phone', Max(Case When (ADR_PHONES.POS = 1 And ADR_PHONES.ADDRESS_PHONE_TYPE Is Not Null) Then ADR_PHONES.ADDRESS_PHONE_EXTENSION Else ADR_PHONES1.ADDRESS_PHONE_EXTENSION End) As 'Business Phone EXT' From PERSON Left Join ADDRESS On PERSON.PREFERRED_ADDRESS = ADDRESS.ADDRESS_ID Inner Join ADDRESS_LS On ADDRESS_LS.ADDRESS_ID = ADDRESS.ADDRESS_ID Inner Join PSEASON On PSEASON.ID = PERSON.ID And PSEASON.PERSON_ADDRESSES = ADDRESS.ADDRESS_ID Full Join PEOPLE_EMAIL On PEOPLE_EMAIL.ID = PERSON.ID Left Join PERPHONE On PERPHONE.ID = PERSON.ID Full Join ADR_PHONES On PERSON.PREFERRED_ADDRESS = ADR_PHONES.ADDRESS_ID Full Join ADR_PHONES ADR_PHONES1 On PERSON.PREF_BUS_ADDRESS = ADR_PHONES1.ADDRESS_ID Left Join PERSON PERSON1 On PERSON.PREF_BUS_ADDRESS = PERSON1.PREFERRED_ADDRESS Where ( PERSON.ID Is Not Null And (ADR_PHONES.ADDRESS_PHONE_TYPE = 'BUS' Or ADR_PHONES.ADDRESS_PHONE_TYPE Is Null) And PEOPLE_EMAIL.POS = 1) Or (PEOPLE_EMAIL.POS Is Null) Group By PERSON.ID, PERSON.PREFIX, PERSON.FIRST_NAME, PERSON.MIDDLE_NAME, PERSON.LAST_NAME, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIP, PSEASON.ADDR_TYPE, PEOPLE_EMAIL.PERSON_EMAIL_ADDRESSES Order By PERSON.ID
t-sqlsql-server-2008-r2query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
the query does a LEFT JOIN to get to ADDRESS for the preferred address. After this, the query does several INNER JOINS to other tables using the ADDRESS table. Each of these inner joins will leave out rows that don't have a primary address. LEFT OUTER JOINS will keep the missing address rows so you can report on them
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

muk avatar image muk commented ·
Thanks @Ken!!! Just what I needed!!!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.