GlideRecord LEFT JOIN two tables

[GaetanoD]

Hi guys,

How Can I write a LEFT JOIN in ServiceNow?

Exist GlideRecord's method called addJoinQuery for INNER JOIN, but not LEFT JOIN.

Can you help me?

Thanks,

Gaetano

[Chuck Tomasi]

Consider taking a look at Database Views and query against that with a traditional GlideRecord query.

http://wiki.servicenow.com/index.php?title=Database_Views

[GaetanoD]

I believe it does not work. I must create a script fix, and not a database view.

Table1:

sys_id,field1,field2 ...

Table2:

sys_id,field1, field2, sys_id_table_1

I have to have all record of the table1 with sys_id_table_1 is null.

[Community Alums]

how did you implement this ?

I need to join two tables in Scripted REST API .. ..!!

[Community Alums]

This is what seems to be working for me (running in Xplore):

var userId = gs.getUserID();

var grSCPE = new GlideRecord('__table1__');
var grJoin = grSCPE.addJoinQuery("__m2mTable__", "sys_id", "__m2mLinkingField__");
grJoin.addCondition("__m2mConditionField__", userId);

// needs to work as OR, so we use New Query
grSCPE.addEncodedQuery("NQ__table1ConditionField__=" + userId);
grSCPE.query();

while (grSCPE.next()) {
gs.addInfoMessage('number: ' + grSCPE.getValue('number'));
}
grSCPE;