Left Joining Problem on Database View

[Andrew Bettcher]

Hi,

I've tried all sorts before coming here so I hope someone can help me.

I have a very simple table report that shows the number of incidents tickets per customer across the top and per assignment group down the side.

As normal, if a customer has no tickets then they don't appear on the table.

The request from my users is to show null values so that we have a complete list of customers across the top even if they have no active tickets.

There are a lot fo posts about this including the seemingly famous "How do I report on nothing?" 

My first instinct was to avoid anything to do with database views because I didn't understand the term "left join". It soon became clear that if I wanted to deliver the report as requested (and use the same solution for multiple other reports going forward) that I would have to learn what "left join" meant and learn about database views.

So, now I have a database view that, thanks to trial and error, shows me some results (in fact ALL incidents).

I joined the incident table (inc) to the core_company table (comp) and specified relevant fields by following the instructions on Docs. All went fine.

Then I spent a few hours using a variety of "where clauses" and combinations of left join = true and I still haven't been able to return a company record that has no tickets assigned to them.

My where clause is:

comp_name=inc_assignment group

I've tried comp_sys_id=inc_almost everything else and vice-a-versa.

My understanding is that the first part of the where clause is "left" and so the where clause us telling ServiceNow to join the company table to the incident table by associating the name field on the former with the assignment group field on the latter.

It still doesn't work.

Any ideas? I promise that if I fix it I will write up a "how to" for dummies.....

[Yifeng Zhang]

Ok so make sure your comp table has a lower order than your incident table so the comp table loads first to facilitate the left join.

I hope it is clear that you want to do a left join from incident table ON TO the comp tables, which means you want all comp record present even if it has no incident record associated to it.

Then the first thing I would create is one database view just contains nothing but the entire comp table, so that way you make sure you are pulling all of the company records, you probably shouldn't have any where clauses on comp table.

Then try to join the incident table onto it, you want to check the left join on the incident table, and all the where clauses specify the conditions on that where clause, not on the comp table.

That should work. let me know if it doesnt.

Database view is amazing tho... don't avoid it XD

[Andrew Bettcher]

Thank you Yifeng.

I started again and did as you recommended and I think I made a step forward as I now have a list of all of my customers (including the ones I know don't have any tickets). 

I didn't add any fields to either so have access to all of the fields on both tables. My where clause is on the incident table and reads:

comp_sys_id=inc_sys_id

Which seemed nice and simple but there is no scientific or logical basis for me thinking this.

The list continues to only show all of the companies and not the tickets (or 0 tickets) associated.

You have helped my understanding immensely. Thank you again. 

[Yifeng Zhang]

First of all, the where clause is incorrect,

you are trying to do join of the comp table and incident table.

now your where clause is saying comp_sys_id = inc_sys_id.

so the database will say,  oh now I will join the incident record whose sys_id is equal to the company's sys_id.

But this will never happen! because the comp.sys_id is storing the ID of the company, not the ID of the incident record.

So you should have a reference field on your comp or incident table that defines the association, for example, incident 1 is related to company A so ideally you would have incident1.relatedCompany = Company A. now the reference field of servicenow platform stores the target record's sys_id, so your where clause should looks like this:

comp_sys_id = inc_u_related_company

That should give you the join.

[Adam Stout]

To find out who doesn't have any tickets, you just need a related list condition.  No database view needed, you just need to use the Report Designer.  Here is an article that explains some use cases using related list conditions:

https://community.servicenow.com/community?id=community_blog&sys_id=bd0eaa2ddbd0dbc01dcaf3231f96199e