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

[-O-]

Yes and no.

I mean between regular tables left join pretty much works as in MySQL.

However it seems queries involving the cmdb tables are heavily re-written.

Cause physically the cmdb is in several table partitions; because if this the select is re-written so that the table involved is transformed into an inner join between the table and a table partition.

If you are curious, just fire up the SQL debugger while running your view.

You will have nightmares for days 🙂

[-O-]

This was supposed to be a view with a single table, so a simple SELECT * FROM cmdb_ci, but it ended up like:

[Robert Campbell]

I believe I understand what you're saying but is this possibly a bug?  A table is a table.  It shouldn't matter that the table name has the word cmdb in it.  If I want to do a query on a table, it should behave as all tables.  There's no need to "re-write" a query just because I'm using a cmdb query.  

How do I access the SQL debugger using Chrome.  When I try, there's nothing there:

[-O-]

Actually a table is not a table.

That is what I have tried to explain towards the beginning.

There is a translation layer between the real DB (where the SQL queries are executed and must be tailored to the actual schema) and what is visible in SN.

This is not so much a bug as a limitation of the ecosystem.

One just can't do SQL as in a real DB.