When joining the tables UPR00100 and UPR00102, you have to consider if you want the employee information and all of their addresses or just the current address displayed on the Employee Maintenance window (Employee Card).

The most common query is to get just the address displayed on the employee card and to do that use the fields EMPLOYID and ADRSCODE from each table to join them together.

 

Example:

SELECT E.LASTNAME, E.FRSTNAME, A.ADRSCODE, A.ADDRESS1, A.ADDRESS2, A.CITY, A.STATE, A.ZIPCODE
FROM UPR00100 AS E
    INNER JOIN UPR00102 as A on E.EMPLOYID = A.EMPLOYID and E.ADRSCODE = A.ADRSCODE
WHERE E.EMPLOYID = 'BARR0001'

This query will return only one record. The Employee information and the address that is currently displayed on the Employee Card.

 

However, if you leave off the “and E.ADRSCODE = A.ADRSCODE” part of the inner join like this:

SELECT E.LASTNAME, E.FRSTNAME, A.ADRSCODE, A.ADDRESS1, A.ADDRESS2, A.CITY, A.STATE, A.ZIPCODE
FROM UPR00100 AS E
    INNER JOIN UPR00102 as A on E.EMPLOYID = A.EMPLOYID
WHERE E.EMPLOYID = 'BARR0001'

In the initial demo company you will actually get two records, one for the primary address (ADRSCODE = ‘PRIMARY’) and one for the work address (ADRSCODE = “WORK”)

 

Now while initially you may think you would never want to do that, what if you are actually creating a warehouse table and wanted both addresses in the same record in the warehouse table. Then you could do something like this.

SELECT E.LASTNAME, E.FRSTNAME,
    PA.ADDRESS1 as PRIMARY_ADDRESS1,
    PA.ADDRESS2 as PRIMARY_ADDRESS2,
    PA.CITY as PRIMARY_CITY,
    PA.STATE as PRIMARY_STATE,
    PA.ZIPCODE as PRIMARY_ZIPCODE,
    WA.ADDRESS1 as WORK_ADDRESS1,
    WA.ADDRESS2 as WORK_ADDRESS2,
    WA.CITY as WORK_CITY,
    WA.STATE as WORK_STATE,
    WA.ZIPCODE as WORK_ZIPCODE
FROM UPR00100 AS E
    INNER JOIN UPR00102 as PA on E.EMPLOYID = PA.EMPLOYID and PA.ADRSCODE = 'PRIMARY'
    INNER JOIN UPR00102 as WA on E.EMPLOYID = WA.EMPLOYID and WA.ADRSCODE = 'WORK'
WHERE E.EMPLOYID = 'BARR0001'

 

However, notice in this “warehouse” query:

  • It was necessary to do two joins to UPR00102 and distinguish between which record from UPR00102 that I wanted by adding the value for ADRSCODE in each of the joins. (Note: This is what Programmers call “Hard Coding” because the ADRSCODE is “Hard Coded” into the query.)
  • It was also necessary to rename the fields from PA.ADDRESS1 to PRIMARY_ADDRESS1 and  WA.ADDRESS1 to WORK_ADDRESS1, etc. so that in your warehouse table would not end up with two fields with the same name. Something that SQL Server really does not like. Winking smile
  • Moreover, you also must know in advance what the possible values for the ADRSCODE field can be. If someone enters a different value (like “PRIM” ) then those records will never make it into your warehouse table. Sad smile

 

So, depending on your exact needs you can now join the two tables either way you wish.

 

Table of Contents