Can someone please provide clarification on left joins and database views? I am not getting expected results. I want to get Incident where assignment group is sys id of helpdesk, and I want to join to sys_user a few times once for assigned to once for caller id etc, and I want ALL incidents from first one where helpdesk but i dont want to bring in all user tables. I have experience with SQL but its not behaving as id expect. Tried a few things and nothing works
Hello again Kaylee,
A simple example of a left join in a database view is joining 2 different tables, let's say incident (inc, 100) and user (usr, 200). A where clause on the second table - inc_assigned_to = usr_sys_id - will show all incidents that have an assigned_to. Adding a left join to this scenario will show all incidents, and the assigned to if there is one. If I switch the order of the tables and where clause I can see all users, and any incidents where they are the assigned to, so I could see more than one row per user.
To see all incidents assigned to a certain group, you would actually join the incident table to itself like this - no left joins
The where clause is
inc1_sys_id = inc2_sys_id && inc2_assignment_group = '<<group sys_id>>'
Adding one join to the User table for assigned_to would look like this
Note that you do want a left join in this case since you want to see all incidents assigned to the helpdesk, whether they are assigned to a user or not. I haven't tried adding further joins on sys_user, but it should follow the same pattern in the where clause - always with a left join so that you continue to see all helpdesk incidents whether they have a ___ user or not. Happy coding!
Hello again Kaylee,
A simple example of a left join in a database view is joining 2 different tables, let's say incident (inc, 100) and user (usr, 200). A where clause on the second table - inc_assigned_to = usr_sys_id - will show all incidents that have an assigned_to. Adding a left join to this scenario will show all incidents, and the assigned to if there is one. If I switch the order of the tables and where clause I can see all users, and any incidents where they are the assigned to, so I could see more than one row per user.
To see all incidents assigned to a certain group, you would actually join the incident table to itself like this - no left joins
The where clause is
inc1_sys_id = inc2_sys_id && inc2_assignment_group = '<<group sys_id>>'
Adding one join to the User table for assigned_to would look like this
Note that you do want a left join in this case since you want to see all incidents assigned to the helpdesk, whether they are assigned to a user or not. I haven't tried adding further joins on sys_user, but it should follow the same pattern in the where clause - always with a left join so that you continue to see all helpdesk incidents whether they have a ___ user or not. Happy coding!
First of all, THANK you. You really are an mvp! This worked great. I was even able to add in multiple entries of task_sla and get different sla definitions all in. I do have 2 follow ups: is there a way to DISTINCT back records? Use case is 2 entries for the same SLA definition when a user reopened a ticket. Second is: someone mentioned a little known fact is that left joins are run LAST no matter the order. how is that possible? Thanks so much!
The DISTINCT concept is not available in Database Views, but 2 workarounds come to mind. Is there always something different about the 2 SLA definitions when a ticket is reopened that you could add to the where clause - either within the task_sla records themselves, or in relation to the incident. I'm not even sure if you could add this to the where clause, but when an incident reopens if you're not clearing closed_at, then the sla start time would be > closed_at. The other workaround has to do with where you are using this view. In reports you could try to add a filter on the view to get rid of these duplicate SLAs, or if you're ready for graduate-level Database View domination, then you may find this approach useful. You can create a Business Rule on the Database View to do any further filtering that you can't do within the view definition itself. So in my basic example, I'm showing all incidents with the certain assignment group, then I've joined task_sla to only show this one definition
If I'm following your use case correctly, in my simplified data mock-up I have the same SLA twice on incident 001, so it's showing 2 rows when I only want to see one row.
Create a before Query Business Rule using the Database View Name as the Table with no Filter Conditions. The Script is going to query the same database view, but only push the dbv sys_id to an array if the sla sys_id is unique for that incident. This new array then determines the records that are shown in a list view.
(function executeRule(current, previous /*null when async*/) {
var inc = '';
var sla = '';
var dbvArr = [];
var dbv = new GlideRecord('u_bkb_all_sd_inc');
dbv.orderBy('inc2_sys_id');
dbv.orderBy('sla_sla.name');
dbv.query();
while(dbv.next()){
if(dbv.inc2_sys_id.toString() == inc){
if(dbv.sla_sla.toString() != sla){
sla = dbv.sla_sla.toString();
dbvArr.push(dbv.sys_id.toString());
}
}
else{
inc = dbv.inc2_sys_id.toString();
sla = dbv.sla_sla.toString();
dbvArr.push(dbv.sys_id.toString());
}
}
current.addQuery('sys_id', 'IN', dbvArr.join(','));
})(current, previous);The glorious results:
