ServiceNow - how to write a selected query that retrieves values ​​from 2 tables

Question

I am new to ServiceNow, but I know SQL , and it just doesn’t make sense that I couldn’t find such a simple query example for ServiceNow on my official website and on Google. Is there a way for JOIN or just check if field X from table 1 is equal to field Y from table2?

Example: I have 2 tables, companies and users, and I need " SELECT " for all users working in London. In the users table, I have the " company_name " field, and in the companies table there are the company_name and city fields.

In SQL, I could solve this with a simple query, for example:

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

or using JOIN:

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

How to do it in ServiceNow? Thanks

Answer 1

Reference fields will handle this for you.

If you use ready-made tables in ServiceNow for the user ( sys_user ) and company ( core_company ), they are linked by the user reference field ( sys_user.company ).

Using the help fields (essentially foreign keys), you can use dot-walk to query through the reference field to query the fields in the specified record. A GlideRecord query to retrieve all users in a London based company would look like this:

 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 with the request via encoded URLs:

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

Either the GlideRecord request or the encoded URL ends up producing SQL under the hood that performs the search you are looking for (you can activate SQL Debug SQL debugging as an administrator 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 actually cannot use these OOB tables, but the relationships you are trying to query against using joins will be resolved with similarly configured reference fields

Answer 2

I just want to add something to the answer above (I will give an example). I will use encodedQuery, it is safer than addQuery ().

 var gr=new GlideRecord('users');//Creating object gr.addEncodedQuery("Paste query after creating from query builder"); gr.query();//Executing query while(gr.next()) { gs.addInfoMessage("User=" + gr.user_name); gs.addInfoMessage("Company Name=" + gr.company.name); gs.addInfoMessage("Company Address=" + gr.company.street); gr.addInfoMessage("Company City=" + gr.company.city); } 

Answer 3

So reporting can help you present your database in ServiceNow:

http://wiki.servicenow.com/index.php?title=Database_Views#gsc.tab=0

The database view defines table joins for reporting purposes.