This example shows the proper settings when using left-joins to add tables to a database view.

Before you begin

Role required: admin

About this task

The following procedure shows how to create a database view that includes a list of Catalog Tasks and their parents. Most of the steps take place on the View Table form.

View Table form in the database view after a left-join has been added.

Procedure

  1. Navigate to All > System Definition > Database Views.
  2. Select the view you want to edit.
    The Database View page appears. In the View Tables related list, specify the tables you want in the database view.
  3. In the View Tables related list, select New.
  4. Add a Left join check box to the form by Configuring the form layout.
  5. Add the Catalog Task [sc_task] table to the database view by filling in the form with the following data and then select Submit. Joined tables are ordered left to right from lowest to highest Order values.
    FieldValue
    Table Catalog Task [sc_task]
    Variable prefix cat
    Order 100
    Where clause Leave blank
    The Database View page appears with the Catalog Task table in the View Tables table.
  6. In the View Tables table, select New and add the Requested Item [sc_req_item] table to the database view by filling in the form with the following data and then selecting Submit. Joined tables are ordered left to right from lowest to highest Order values.
    FieldValue
    Table Requested Item [sc_req_item]
    Variable prefix item
    Order 200
    Where clause cat_parent=item_sys_id
    Left join check box True
    The Database View page appears with the Requested Item table in the View Tables table.
  7. In the View Tables table, select New and add the Request [sc_request] table to the database view by filling in the form with the following data and then selecting Submit.
    FieldValue
    Table Request [sc_request]
    Variable prefix req
    Order 300
    Where clause cat_parent=req_sys_id
    Left join check box True
    The Database View page appears with the Request table in the View Tables table.
  8. In the View Tables table, select New and add the User [sys_user] table to the database view by filling in the form with the following data and then selecting Submit.
    FieldValue
    Table User [sys_user]
    Variable prefix user
    Order 400
    Where clause cat_opened_by=user_sys_id
    Left join check box False
    • The Database View page appears with the User table in the View Tables table.
    • If the parent record of Catalog Task is a Requested Item, all the fields in the Request table will be blank.
    • If the parent record is a Request, all the fields in Requested Item will be blank.
    • Because of the two left joins, the Catalog Task record returns even if the parent is empty or is not a Requested Item or Request.
    • Because User is not a left join, there must be a matching user in sys_user for the row’s Opened By field for the Catalog Task row to return.
  9. (Optional) Perform the following steps to experiment and learn more about how left joins impact database views.
    If you make the left join in:
    • User true, rows that have an empty Created By return.
    • Requested Item false, only Catalog Tasks that have a Requested Item as a parent return.
    • Request false, only Catalog Tasks that have a Request as a parent return.
    • Requested Item and Request false, no rows return because nothing can have a Requested Item and a Request as parents.