SELECT A.* FROM TableA AS A LEFT JOIN TableB AS B ON A.id = B.table_id WHERE B.table_id IS NULL;

[Meeravali]

How we will achieve this in servicenow, here A table can by table. it is dynamic, i want generate the above query using gliderecord script

[Rafael Batistot]

Hi @Meeravali 

function getUnlinkedRecords(tableA, tableB, refField) {
// Step 1: Query tableB to get all referenced IDs
var bGR = new GlideRecord(tableB);
bGR.query();

var linkedIds = [];
while (bGR.next()) {
linkedIds.push(bGR.getValue(refField));
}

// Step 2: Query tableA where ID is not in linkedIds
var aGR = new GlideRecord(tableA);

if (linkedIds.length > 0) {
aGR.addQuery('sys_id', 'NOT IN', linkedIds.join(','));
}
// else no linked records, just get all records from A

aGR.query();

var results = [];
while (aGR.next()) {
results.push(aGR.getDisplayValue()); // or use aGR.getValue() for specific fields
}

return results;
}

// Example usage:
var unlinkedRecords = getUnlinkedRecords('table_a', 'table_b', 'table_id');
gs.info(JSON.stringify(unlinkedRecords));

Explanation:

• tableA → Main table (A in SQL)
• tableB → Table to LEFT JOIN (B in SQL)
• refField → The reference field in tableB pointing to tableA (B.table_id)
• NOT IN is the GlideRecord way of doing “LEFT JOIN WHERE IS NULL”

[jominjohncy]

can you explain the requirement? since you are querying all records in table this either is a scheduled job or a one time requirement. If this is a one-time requirement, the simplest way is to export the data from both tables into Excel and use a VLOOKUP/MATCH to spot the missing relationships.

If this needs to be automated or scheduled, then you’d handle it in ServiceNow using either:

• A Database View with a LEFT JOIN and filter for NULLs, or

• A GlideRecord/GlideAggregate script that collects all A records and excludes those found in B.

[Meeravali]

We have a two tables, A and B, B has the document_id type field of A.   I want  matched and unmatched records of A for two different use cases.  

it's not one time requirement,  i want write a script include/java code where table A is dynamic table(means any table)

[Ravi Gaurav]

Hi @Meeravali 
----------
SQL QUERY :
SELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B ON A.id = B.table_id
WHERE B.table_id IS NULL;
-----------------------------

SERVICENOW CONVERSION :

var grA = new GlideRecord('table_a'); // Name of TableA
// If TableB.table_id references A.sys_id:
grA.addNotExistsQuery('table_b', 'table_id');

// If TableB.table_id references some other unique field (e.g., A.id):
grA.addNotExistsQuery('table_b', 'table_id', 'id');

grA.query();
while (grA.next()) {
gs.info('Record from A with no match in B: ' + grA.getValue('id'));
}