SELECT A.* FROM TableA AS A LEFT JOIN TableB AS B ON A.id = B.table_id WHERE B.table_id IS NULL;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-22-2025 01:17 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-22-2025 01:30 AM
Hi @MeeravaliP
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”
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-22-2025 03:09 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-24-2025 11:47 PM
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-22-2025 04:19 AM
Hi @MeeravaliP
----------
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'));
}