Thaddeus Rix Thaddeus Rix - 5 months ago 10
SQL Question

SQL - How do I use nested joins

I'm working on gathering information from my server but the query I've written doesn't return all the values I'm looking for. How would I go about using different types of nested joins or queries to get all the data I'm looking for?

The following snippet returns the total list of employee numbers I'm looking for:

select papf.employee_number, papf.full_name, paaf.person_id

from per_all_people_f papf, per_all_assignments_F paaf

where sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and paaf.person_id > '0'
and papf.employee_number > '0'
and papf.person_id = paaf.person_id
order by 1;


Unfortunately this next snippet, while returning all the columns I want, doesn't return all the rows which I previously was getting from the above query:

select distinct loc.TOWN_OR_CITY city
,COALESCE(CASE WHEN (loc.location_code = 'Foo-Bar') THEN 'Foo-Bar' END, SUBSTR(loc.LOCATION_CODE, 1, INSTR(loc.LOCATION_CODE,'-','1','1')-1), loc.LOCATION_CODE) company
,loc.country
,papf.start_date created
,TRIM(SUBSTR(hou.name, INSTR(hou.name, '-','1','1')+1)) Department
,SUBSTR(pap.name, 1, INSTR(pap.name,'-','1','1')-1) Description
,CONCAT(COALESCE(papf.known_as, papf.first_name), concat(' ', papf.last_name)) DisplayName
,hou.attribute5 Division
,papf.employee_number EmployeeNumber
,papf.first_name givenname
,papf2.full_name Manager
,CONCAT(COALESCE(papf.known_as, papf.first_name), concat(' ', papf.last_name)) name
,pp.phone_number OfficePhone
,papf.known_as OtherName
,loc.region_2 state
,loc.postal_code postalcode
,papf.last_name Surname
,pp.phone_number Telephone#
from per_all_people_f papf
,per_all_positions pap
,per_phones pp
,per_all_people_f papf2
,per_all_assignments_f paaf
,hr_organization_units hou
,hr_locations_all loc
where
papf.person_id = paaf.person_id
and papf2.person_id = paaf.supervisor_id
and paaf.organization_id = hou.organization_id
and loc.location_id = paaf.location_id
and sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between paaf.effective_start_date and paaf.effective_end_date
and sysdate between papf2.effective_start_date and papf2.effective_end_date
and pp.parent_id = papf.person_id
and phone_type = 'Work Phone'
and paaf.position_id = pap.position_id
;


From all the posts I've read, I'm under the impression that the work I need to do is in the FROM clause but none of the attempts I've made so far have panned out.

I would greatly appreciate any information you can provide on how it's possible to get a new 'table' from a join (such as the working example in my first query), then only reference that table for the remaining attributes. The first query returns over 5,000 results while my seconds only returns 2,500. I need all the columns from the second query without sacrificing any rows which are returned from the first query. I don't mind if there are null values for some columns.

Answer

I started by changing to JOIN syntax instead of all the WHERE criteria.
Then, guessed that the phone type was from the "pp" table alias.

select distinct 
      loc.TOWN_OR_CITY city,
      COALESCE( CASE WHEN (loc.location_code = 'Foo-Bar') 
                     THEN 'Foo-Bar' END, 
                SUBSTR(loc.LOCATION_CODE, 1, INSTR(loc.LOCATION_CODE,'-','1','1')-1), 
                loc.LOCATION_CODE ) company,
      loc.country,
      papf.start_date created,
      TRIM(SUBSTR(hou.name, INSTR(hou.name, '-','1','1')+1)) Department,
      SUBSTR(pap.name, 1, INSTR(pap.name,'-','1','1')-1) Description,
      CONCAT( COALESCE(papf.known_as, papf.first_name), concat(' ', papf.last_name)) DisplayName,
      hou.attribute5 Division,
      papf.employee_number EmployeeNumber,
      papf.first_name givenname,
      papf2.full_name Manager,
      CONCAT(COALESCE(papf.known_as, papf.first_name), concat(' ', papf.last_name)) name,
      pp.phone_number OfficePhone,
      papf.known_as OtherName,
      loc.region_2 state,
      loc.postal_code postalcode,
      papf.last_name Surname,
      pp.phone_number Telephone#
   from
      per_all_people_f papf
         JOIN per_phones pp
            ON papf.person_id = pp.parent_id
           and pp.phone_type = 'Work Phone'         
         JOIN per_all_assignments_f paaf
            ON papf.person_id = paaf.person_id
           and sysdate between paaf.effective_start_date and paaf.effective_end_date
           JOIN per_all_people_f papf2
               ON paaf.supervisor_id = papf2.person_id 
              and sysdate between papf2.effective_start_date and papf2.effective_end_date
            JOIN hr_locations_all loc
               ON paaf.location_id = loc.location_id 
            JOIN hr_organization_units hou
               ON paaf.organization_id = hou.organization_id
            JOIN per_all_positions pap
               ON paaf.position_id = pap.position_id
   where
      sysdate between papf.effective_start_date and papf.effective_end_date

Now, why are you missing missing records? I can only offer the fact that the additional tables you have joined may OR NOT have records based their criteria. I start by the join to the PAPF2 table based on the supervisor. What about the top person. Does EVERYONE have a supervisor? If not because you could have distinct managers of different departments, may not explicitly have a supervisor. In that case, these people would be out of the list. To correct this, change the JOIN above to LEFT JOIN meaning if there IS NO supervisor based on the join, then still include the PAAF record, but know the PAPF2 value(s) would be null returned, but at least they will be returned.

Similarly for your PHONE criteria. You are looking explicitly for a Work Phone, but what if there are some people that the work phone entry has not been provided (yet)? Make that a LEFT JOIN too.

So, you MIGHT want to change all the JOINs above to LEFT JOIN and make sure you get all the records you intended, without respect to all the related tables having records or not.

Then, one JOIN at a time change it from LEFT JOIN back to JOIN and see how many records become removed from the result set. This will tell you that there are no matches for certain records.

Now that you know this is the case, who are these people? To resolve that, change it back to a LEFT JOIN, and in the WHERE clause add a criteria EXPECTING a NULL. For example, the first example on supervisors. Change the query to all LEFT JOIN. Then for the WHERE clause, add the following...

AND papf2.person_id IS NULL

So, this is saying, left join giving me all records regardless of a match. Now, from that, I ONLY WANT those records where the supervisor's person ID as a result of the left join IS NULL, meaning not found. So now you can see all people who DO NOT have a supervisor and either confirm, or adjust to apply proper supervisor status on these people.

Then, try again for phones... remove the first AND clause above and then add...

AND pp.parent_id IS NULL

If the person HAS a work phone, we dont care. We only want to get list of people who DO NOT have the phone record.

Hope this makes sense...