How to Write a Select Query Retrieving Values from Two Tables in ServiceNow

If you’re new to ServiceNow but familiar with SQL, you might find it challenging to translate SQL JOIN queries into ServiceNow’s GlideRecord queries. ServiceNow has its own way of handling data relationships, which can be different from traditional SQL but is equally powerful. In this post, we’ll explore how to retrieve values from two tables in ServiceNow, similar to how you would with SQL JOINs.

The SQL Way:

Let’s say you have two tables: companies and users, and you want to select all users who work in London. In SQL, you could write:

SELECT u.* 
FROM users u, companies c
WHERE u.company_name = c.company_name 
AND c.city = 'London';

Or using a JOIN:

SELECT u.* 
FROM users u
LEFT JOIN companies c ON u.company_name = c.company_name
WHERE c.city = 'London';

The ServiceNow Way:

In ServiceNow, you use the GlideRecord class to query tables. Here’s how you can achieve the same result.

Understanding Reference Fields:

ServiceNow uses reference fields (similar to foreign keys) to link tables. For example, the sys_user table (users) and the core_company table (companies) are linked by a reference field sys_user.company.

With reference fields, you can use dot-walking to query through a reference field to access fields on the referenced record.

Using GlideRecord:

Here’s an example of how you can write a GlideRecord query to retrieve all users in a company located in London:

var user = new GlideRecord('sys_user');
user.addQuery('company.location', 'London');
user.query();
while (user.next()) {
  gs.info("User: " + user.user_name);
  gs.info("Company: "  + user.company.getDisplayValue('name'));
  gs.info("Company Location: " + user.company.getDisplayValue('location'));
}

This query uses dot-walking through the company reference field on the sys_user table to filter users based on the location field in the core_company table.

Encoded Queries:

You can also use an encoded query to achieve the same result, which is often simpler and safer:

var gr = new GlideRecord('sys_user');
gr.addEncodedQuery('company.location=London');
gr.query();
while (gr.next()) {
  gs.info("User=" + gr.user_name);
  gs.info("Company Name="  + gr.company.getDisplayValue('name'));
  gs.info("Company Location=" + gr.company.getDisplayValue('location'));
}

Using GlideAggregate for Aggregation:

If you need to perform aggregation, such as counting the number of users in each city, you can use GlideAggregate:

var ga = new GlideAggregate('sys_user');
ga.addAggregate('COUNT', 'sys_id');
ga.groupBy('company.location');
ga.query();
while (ga.next()) {
  gs.info("Location: " + ga.getValue('company.location') + ", Count: " + ga.getAggregate('COUNT', 'sys_id'));
}

While ServiceNow’s GlideRecord might seem different from SQL at first, understanding reference fields and using dot-walking effectively can help you perform complex queries and data retrieval operations. Whether you choose to use direct GlideRecord methods, encoded queries, or GlideAggregate for aggregation, these techniques allow you to navigate relationships between tables and retrieve the data you need efficiently.