ServiceNow - how to write a select query retrieving values from 2 tables

Question

I'm new to ServiceNow but I know SQL and it just doesn't make sense that I was unable top find such a simple query example for ServiceNow on their official site and google. Is there any way for JOIN or just checking if X field from table 1 equals Y field from table2?

Example: I have 2 tables, companies and users, and I need to "SELECT" all users that work in London. In users table i have a field 'company_name' and in companies table I have fields company_name and city.

In SQL I could solve it with simple query like:

SELECT u.* from users u, companies c
WHERE u.company_name = c.company_name and c.city = 'London'

or with JOIN:

SELECT u.* from users u
LEFT JOIN companies c on u.company_name = c.company_name
WHERE c.city = 'London'

How to do this in ServiceNow? Thanks

Answer 1

Reference fields would handle this for you.

If you're using the out-of-box tables in ServiceNow for user (sys_user) and company (core_company), they are linked by a reference field on user (sys_user.company).

With reference fields (essentially foreign keys), you can use dot-walking to query through a reference field to query against fields on the referenced record. The GlideRecord query to retrieve all users in a company located in London would look like:

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

You can do the same thing with querying through encoded URLs:

yourinstance.service-now.com/sys_user_list.do?sysparm_query=company.city=London

Either the GlideRecord query or the encoded URL end up producing SQL under the hood that does the join you're looking for (You can active Debug SQL session debugging as an admin to see the generated sql):

SELECT ... 
FROM  sys_user LEFT JOIN 
      core_company ON sys_user.company = core_company.sys_id 
WHERE core_company.city = 'London'

Now, you may not actually be using these OOB tables, but the relationships you're trying to query against using joins would be solved with similarly configured reference fields