x

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
more ▼

asked Jul 11, 2012 at 07:33 PM in Default

avatar image

muk
440 33 35 40

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 11, 2012 at 08:53 PM

avatar image

KenJ
25k 3 10 20

Thanks @Ken!!! Just what I needed!!!

Jul 11, 2012 at 08:56 PM muk
(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.

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:

x1089
x776
x449

asked: Jul 11, 2012 at 07:33 PM

Seen: 735 times

Last Updated: Jul 11, 2012 at 08:56 PM

Copyright 2017 Redgate Software. Privacy Policy