SQL JOIN

The following scenario describes how use JOIN to combine rows from two or more tables based on a common column between them.

Scenario

Information is missing in your Invoice report and you cannot find the information in the Invoices table. Furthermore, you have a column called CREATOR_ID, but the column contains a number pointing to an ID in another table where all the User information is stored. You also have a column called PROJECT_ID which points to another table where all the Project Information is stored.

Solution

To find the table, follow these steps:

Note: You can use other SQL Joins to conduct the following.

Use a LEFT JOIN since this option returns all the rows from the left table (for example, Invoices) even if there are no matches in the right table (for example, User info table, Project info table, etc.).

To add the Invoice creator’s name, match your CREATOR_ID column from the Invoices table to you Users table (unifier_sys_user_info_view). You can do so by using a LEFT JOIN on the USERID column of the User table. Assign an alias (sysuser) to add the information to the end of your statement:

Example

LEFT JOIN unifier_sys_user_info_view sysuser

ON (inv.CREATOR_ID = sysuser.USERID)

Look at the ER Views (System Models View) to see what options are available in your sysuser table.

Grab the following columns from the table: sysuser.FULLNAME, sysuser.EMAIL, and sysuser.WORKPHONE.

Add the columns to the end of the SELECT section of your query. Each column specified in the SELECT portion must have a comma after it, except for the last one.

Access the information about the Project or Shell (name, number, start/end dates, etc.). You can find this information in the table unifier_us_p, with the column proj.PID that is used to join the two tables.

Example

LEFT JOIN unifier_us_p proj

ON (inv.PROJECT_ID = proj.PID)

Grab the following columns from the table. and add the columns to the end of the SELECT section, before the FROM, of your query. Each column specified in the SELECT portion must have a comma after it, except for the last one.

Example

proj.UGENPROJECTNAME --> AS ProjectName,

proj.UGENPROJECTNUMBER --> AS ProjectNumber,

proj.UUU_PROJECT_START_DATE --> AS ProjectStartDate,

proj.UGENPROJENDDATEDO --> AS ProjectEndDate,

proj.UGENINITBUDGETCA --> AS InitialBudget,

proj.UGENREVBUDGETCA --> AS RevisedBudget

Ensure that you see the following codes:

Example

SELECT inv.PROJECT_ID,

inv.ID AS inv_ID,

inv.RECORD_NO AS inv_record_no,

inv.TITLE AS inv_title,

inv.STATUS AS inv_status,

inv.UVEVENDORNAMETB50 AS vendor,

inv.AMOUNT AS inv_amount,

sysuser.FULLNAME,

sysuser.EMAIL,

sysuser.WORKPHONE,

proj.UGENPROJECTNAME --> AS ProjectName,

proj.UGENPROJECTNUMBER --> AS ProjectNumber,

proj.UUU_PROJECT_START_DATE --> AS ProjectStartDate,

proj.UGENPROJENDDATEDO --> AS ProjectEndDate,

proj.UGENINITBUDGETCA --> AS InitialBudget,

proj.UGENREVBUDGETCA --> AS RevisedBudget

FROM unifier_ui inv

LEFT JOIN unifier_sys_user_info_view sysuser

ON (inv.CREATOR_ID = sysuser.USERID)

LEFT JOIN unifier_us_p proj

ON (inv.PROJECT_ID = proj.PID)



Legal Notices
Copyright © 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Last Published Wednesday, March 28, 2018