Example left join in creating a database view

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.

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.

   Field	Value
   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.

   Field	Value
   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.

   Field	Value
   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.

   Field	Value
   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.
   • 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.

Parent Topic:Joining tables using database views

Previous topic:Add a table to the database view

Next topic:Specify a field to return