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.....

[lagravere]

Hi Andrew,

I'm agree with yifeng

I'm not sure to understand your where clause, you have to join the 2 table with company_sys_id=incident_company_sys_id

[Andrew Bettcher]

Thank you.

Adam - It's not about showing who doesn;t have incidents, it;s about displaying everyone on a table on a report regardless of whether they have tickets or not.

I added the "company" field to the table view and the made my where clause:

comp_sys_id=inc_company

(because company is the closest thing I have to incident_company_sys_id). Report comes back with only companies that have at least one ticket.

Unless you are saying that I need to actually add a new field to the incident table taht shows the company sys_id?

I feel like I'm on the brink of getting my head around this so thank you so much for all your help.

I lvoe ServiceNow. It's a great community.