How do you do left joins?

[Robert Campbell]

I want to see all records from the cmdb_ci table that have u_core_ci = true and sys_class_name = cmdb_ci_service OR u_cmdb_ci_business_application.  I want to see where those records show up on the cmdb_rel_ci table as a child where the parent is of the class cmdb_ci_business_app.  I want to use this to get a report that will show data from both the cmdb_ci table and cmdb_ci_business_app table.  My MySQL query gives the correct results.  I can't tell if the Background Script is giving the correct results because I can't get it to show the value from the cmdb_ci_business_app table or the parent from the cmdb_rel_ci table.  The view I created times out.

MySQL:

select 
  c1.name
  ,r1.parent 
from 
  cmdb_ci c1 
left join 
  cmdb_rel_ci r1 
on 
  r1.child = c1.name 
left join 
  cmdb_ci_business_app b1 
on 
  b1.name = r1.parent 
where 
  c1.u_core_ci = 'true' 
  and c1.sys_class_name='Business Service' 
    or c1.sys_class_name='Business Application';

If I export the tables and run this, it works correctly.

Background Script:

var c1 = new GlideRecord('cmdb_ci');
c1.addQuery('sys_class_name','cmdb_ci_service').addOrCondition('sys_class_name','u_cmdb_ci_business_application');

var r1 = c1.addJoinQuery('cmdb_rel_ci','sys_id','child');
var b1 = r1.addJoinQuery('cmdb_ci_business_app','parent','sys_id');

c1.query();

while(c1.next()) {
gs.print(c1.name+ ' ' +c1.sys_class_name+ ' ' +b1.name);
}

gs.print(c1.getRowCount());

Database View

Name: u_business_application_and_business_service

Table	Active	Left Join	Order	Variable prefix	Where clause
cmdb_ci	true	true	100	c1	(c1.sys_class_name='cmdb_ci_service' || c1.sys_class_name='u_cmdb_ci_business_application') && c1.u_core_ci=true
cmb_rel_ci	true	true	200	r1	r1.child=c1.sys_id
cmdb_ci_business_app	true	true	200	b1	b1.sys_id=r1.parent

I've tried all combinations of left join = true with no success.  I would think only cmdb_rel_ci would be left join true but that doesn't work

[Brad Bowman]

If you want to see all of the CIs that meet the criteria regardless of relationship then switch the r1 where clause to c1.sys_id=r1.child, and put a left join on 200 and 300.  If it doesn't work with this configuration, try taking the where clause off of 100 so that you have the correct - albeit too many - records, then use the Business Rule to do what the where clause is unable to in this case.

[Robert Campbell]

This should only show business service to the left and business applications or nothing on the right.

I'm not sure how left join works in ServiceNow but I would think this would say

1. show all records from the relationship table where c1_sys_id=r1_child regardless of class because there is no other criteria on 200.  However, c1 should only be the classes that are identified in c1 (100)
2. Show all cis that match the criteria on 100.

My assumption is that when using left join in ServiceNow should be the same as using it in MySQL where the table to the left (next lowest order number) will show all rows.

By removing the where clause on c1, I can't tell for sure what's right.

I built this piece by piece and everything works as expected until I try to add the left join.

This shows only the records I want to see.

Then I added the next

This shows what I expect to see

Those are the correct relationships but I don't want to see the relationships where the parent is a service, only when the parent is a business application

Finally I add the business application table

It now only shows the one record because only one of them is related to a business application.

I would think that I would put the join on r1/200 because I want to see all of the records from the where clause above it but I get an error.

Why is it complaining about r1.parent when r1.parent is the right join?

[-O-]

So you say

1. I want to see all the cis in the class of cmdb_ci_service, cmdb_ci_business_app, u_cmdb_ci_business_application.
2. I also want to see where the above cis are mapped to cmdb_ci_business_app or u_cmdb_ci_business_application

Do you mean that u_cmdb_ci_business_application can be mapped to u_cmdb_ci_business_application?

But if we only consider the initial requirements, you don't need left join here, @Brad Bowman is right.

What left join does is to select all records from the "left" table, regardless if there is a related "right" table.

Which contradicts the original premise that you want to see records where there is a child ("left" table cmdb_ci_service or u_cmdb_ci_business_application) and a parent ("right" table cmdb_ci_business_app).

Left join would mean that you want to include all cmdb_ci_services and u_cmdb_ci_business_applications ("left" table), even if those are not related to any cmdb_ci_business_apps ("right" table), where the former are children and the latter are parents.

Which by the way contradicts the latest requirements.

Originally you wanted:

cmdb_ci_service (child)	cmdb_ci_business_apps (parent)
u_cmdb_ci_business_application (child)	cmdb_ci_business_apps (parent)

In the latest requirements you seem to require:

cmdb_ci_service (child or parent)	u_cmdb_ci_business_application (child or parent)
cmdb_ci_service (child or parent)	cmdb_ci_business_apps (child or parent)
u_cmdb_ci_business_application (child or parent)	u_cmdb_ci_business_application (child or parent)
u_cmdb_ci_business_application (child or parent)	cmdb_ci_business_apps (child or parent)
cmdb_ci_business_apps (child or parent)	u_cmdb_ci_business_application (child or parent)
cmdb_ci_business_apps (child or parent)	cmdb_ci_business_apps (child or parent)

Which is it?

[Robert Campbell]

---

Do you mean that u_cmdb_ci_business_application can be mapped to u_cmdb_ci_business_application?

Yes.  There are a couple that are parents of the other.

The child classes are:

• cmdb_ci_service
• u_cmdb_ci_business_application

The parent classes are:

• u_cmdb_ci_business_application
• cmdb_ci_business_app

The child classes should only be those that have u_core_ci=true (1).

Because we want to see all of the children regardless of if it has a parent, we need the left join.

[Robert Campbell]

I see what you're saying.  My last table is cmdb_ci_business_app not cmdb_ci with a where clause of b1_sys_class_name='cmdb_ci_business_app' || b1_sys_class_name='u_cmdb_ci_business_application'.  I figure if I can get it to work with cmdb_ci_business_app then making that change shouldn't break the query but maybe I should just put the effort in now rather than getting this to work and then making that change and having it break for some reason.

My thought is just that for some reason, I'm not understanding how left join works in ServiceNow but with all of the attempts, I think it might not work right.