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

[treidfrb]

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 · Accepted solution]

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.

[treidfrb]

Hi Brian thanks for the reply.

I thought I tried all possible variations, but I will take your example and give it a try.

I like how you added left join to the list view, so thanks for that idea.

Will see if I can make it work.

Thank you!

Tim

[Brian Lancaster]

And to clarify left joins are for when you have data in one table but not in the other. So if in my case if I did not have the left join it would only return request items that had a task but in my case I wanted a view that would give me all request items whether they had a task or not.

[treidfrb]

Hi Brian,

I am marking this as the correct answer.  In your example I would expect you get all RITMs output whether there are any SCTASKs associated or not.  That is my exact use case, sorry if my other reply was confusing.

I really appreciate the assist, I had the concept right but did not have the proper left join configuration.  It works now and I get the full APM table row dump plus the iteration of the lifecycle version date relative to the app rows where they have the referenced app field on the model lifecycle table.

I would share a screen shot but I have to be careful what data I am sharing.

Thanks!!

Tim