Left outer join with GlideRecord

[Panagiotis Kons]

Hello,

I'm trying to join two tables (A & B). Table B has a reference to table A, but not the other way round. With a JOIN query I would normally do

SELECT * FROM A, B where B.ID_THAT_REFERENCES_A = A.ID

This would give me all the records in A that have a corresponding record in B.

However I would like to get all records in A that do not have a corresponding record in B. Wiwth SQL I would do the following

SELECT * FROM A LEFT OUTER JOIN B ON B.ID_THAT_REFERENCES_A = A.ID WHERE B.ID_THAT_REFERENCES_A IS NULL

I am trying to do the same with a GlideRecord in Service now, but for some reason I am not getting the correct results. I'd appreciate any help/pointers on the correct syntax.

Regards

Panos

[Ahmmed Ali]

Hello @Panagiotis Kons 

You can use database view to join both the tables and make left join. Please refer: https://docs.servicenow.com/bundle/utah-platform-administration/page/use/reporting/task/t_CreateADat... 

Thanks,

Ali

[Panagiotis Kons]

Hello Ali,

thank you for your quick reply. I don't want to use a database view, I want to have it in a REST call, so I am trying to implement a scripted REST resource.

Thank you again.

Panos

[Ahmmed Ali]

Hello @Panagiotis Kons 

You can create database view and write GlideRecord on database view in your scripted API to read the table data. Only change will be to prefix respective table prefix in every field name in script.

Example script:

var gr = new GlideRecord('incident_sla');
gr.addEncodedQuery('inc_sys_created_onONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()');
gr.query();

while (gr.next()) {
gs.print(gr.getValue("inc_number"));
}

Thanks,

Ali

[Ahmmed Ali]

Second way is to add below encoded query in GlideRecord, below example is for location and user tables. User has reference to location and below query returns the locations where there are no user records.

RLQUERYsys_user.location,=0^ENDRLQUERY

i.e var gr = new GlideRecord("cmn_location");

gr.addencodedQuery("RLQUERYsys_user.location,=0^ENDRLQUERY");

Above, sys_user is user table name and location is the reference field name which is referring to location table. You can build similar query for your tables.

Note: validate syntax for GlideRecord above.

Thanks,

Ali