How to create a database view that favors one of the tables and includes all records, not just the query matches?

treidfrb · ‎02-17-2022

I have created a lot of database views, they are very handy. I am not a DBA but my understanding of related data in multiple tables is good. I got an assignment to create a report on our app table (APM, cmdb_ci_business_app) and include model lifecycle records. This is a custom table with a reference field to the APM table, which allows 1:n grouping of app to model lifecycle records. When I create a DB view with the app table and the model lifecycle table, I only get the records that match, which is about 1/3 of the app records. I have scripted this easily with an outer GlideRecord loop of the app table, and an inner loop of the model lifecycle table for matches on app sys_id. Of course to be able to report on this via dashboards, I need the data in a table, and the only way to put a bow on it with the script is to create a custom table, which we want to avoid.

I have tried changing the order of the two tables in the DB view, and I tried setting left join on the app table, both times I received the same record count. I don't think setting left join on the model lifecycle table is the right thing to do, but maybe a DBA with more knowledge of SQL has some input on this. I did also try switching the orders and setting left join on the app table, but not the model lifecycle table.

cmdb_ci_business_app 100 app app_sys_id = model_u_application

u_cmdb_model_lifecycle 200 model

-------------------------------------------------------------------------------------------------------

u_cmdb_model_lifecycle 100 model model_u_application = app_sys_id

cmdb_ci_business_app 200 app

Thanks

Tim

Brian Lancaster · ‎02-17-2022

When doing a left join you need to reverse your were clause and set that table with left join true. Also the table that does not have left joint as true should have a lower order. Here is an example where I'm doing a joint on sc_req_item and sc_task as we have a type of RITM that allows users to create the task. I wanted to be able to create a report on RITMs that no tasks were created.

View solution in original post

treidfrb · ‎02-17-2022

So that it's clear the behavior I am seeing vs. that which has been requested.

The app table has ~1500 non-Retired rows

The DB View produces ~400 rows

The Model Lifecycle table has about as many rows as the DB View produces

The ask is for a report of the app table with the Model Lifecycle data added to each app.

I can do this via script but we get dinged for a custom table for that.

If the DB View could produce the data from one of the tables whether the other works in the join or not, that would be the magic that would solve the problem.

Thanks

Brian Lancaster · ‎02-17-2022

Maybe I'm not understanding this correctly. But It sound like you need to build the report off the Model Lifecycle table and dot walk to App fields via the reference to field for the app table.