Left Outer Join Tricks
Left Outer Joins are useful tools! Here are two tricks to get around some issues you may discover.
A left outer join lets two tables be joined in the following manner: All rows from the “left” table are returned, even if a match cannot be found on the “right” table. This differs from normal joins (called an inner join), where a match has to be found for both tables (assuming you did the WHERE criteria correctly).
Practical Example: Left Outer Join
If you had an audit table with the userid in a field such as AUDIT_OPRID, and you wanted the link to the User Profile (PSOPRDEFN) table to get their full name, use a Left Outer Join. Depending on how your site handles User Profiles, you may have decided to delete the User Profile for any users no longer in the system. A Left Outer Join can assist: link the audit table via a left outer join to the PSOPRDEFN table. If a match can be found, you can display the user’s full name (field is OPRDEFNDESC). If a match isn’t found, you still get the audit record’s row, even if the user’s full name isn’t in the query results.
Trick 1: Linking Limitations
Up until version Tools 8.53, you had to outer join a new table based on the last table that was added. In other words, if you had three tables (A, B, and C), and wanted to outer join a new table (D) to the query, it had to be for table C. If you wanted to outer join to table A or B, most people thought you had to rebuild the query (and put them in the right order). There was an issue with this too: what if you wanted to link A to B (via outer join), then A to C (via outer join). The following trick will allow you to Left Outer Join (prior to Tools 8.53), without having to rewrite the query.
If you need to link A to a new table via a left outer join, but there are other tables between A and the next table letter: add table A again (which would be table D in our three table example). Make the link be inner join, and link using the common keys of table A (they are the same table, so they better have commons keys…). Then, add your new table (which would be table E), and select left out join to table D, and finish by selecting the fields to join E to D. This trick is only needed if your Tools enforces that Left Outer Joins must be performed on the last table already in the query.
Trick 2: Linking Assistance Gone Wrong
No matter how you do the Left Outer Join, be aware when PeopleSoft auto populates part of the Criteria for you. For example, several tables automatically apply the EFFDT logic, which is helpful in quickly writing the PS Query. But for a Left Outer Join, this can cause an issue; the EFFDT logic auto added does not follow the Left Outer Join rules, and it in effect makes the new table linked via an Inner Join. If you review the results, you’ll discover that the Left Outer Join table is acting as an Inner Join. An easy fix is to go into the criteria of the EFFDT via the Edit button, and at the bottom of the Edit window, there is a drop down choice to make this criteria be part of the appropriate Left Outer Join.
