Example left join in creating a database view

Previous topic | Next topic | Building applications > Planning your application > Creating database views for reporting >

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

Role required: admin

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.

Navigate to All > System Definition > Database Views . 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. In the View Tables related list, select New . Add a Left join check box to the form by Configuring the form layout . 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. 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. 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. 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.

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.

Parent Topic: Database view creation Previous topic: Add a table to the database view Next topic: Specify a field to return