Scoped GlideRecord - addJoinQuery(String joinTable, Object primaryField, Object joinTableField)

Adds a filter to return records based on a relationship in a related table.

You can use this method to find all the users that are in the database group via the [sys_user_grmember] table, or to find all problems that have an assigned incident via the incident.problem_id relationship.

This is not a true database join; rather, addJoinQuery adds a subquery. So, while the result set is limited based on the join, the only fields that you have access to are those on the base table (those which are in the table with which the GlideRecord was initialized).

Always test queries on a sub-production instance prior to deploying them on a production instance. An incorrectly constructed encoded query, such as including an invalid field name, produces an invalid query. When the invalid query is run, the invalid part of the query condition is dropped, and the results are based on the valid part of the query, which may return all records from the table. Using an insert(), update(), deleteRecord(), or deleteMultiple() method on bad query results can result in data loss.

You can set the glide.invalid_query.returns_no_rows system property to true to have queries with invalid encoded queries return no records.

Table 1. Parameters
Name Type Description
joinTable String Table name
primaryField Object (Optional) If other than sys_id, the primary field
joinTableField Object (Optional) If other than sys_id, the field that joins the tables.
Table 2. Returns
Type Description
GlideQueryCondition A filter that lists records where the relationships match.

Find problems that have an incident attached. This example returns problems that have associated incidents. However, it won't pull values from the incidents that are returned as a part of the query.

var prob = new GlideRecord('problem');
prob.addJoinQuery('incident');
prob.query();

Find inactive problems with associated incidents

// Look for Problem records that have associated Incident records
var gr = new GlideRecord('problem');
var grSQ = gr.addJoinQuery('incident');
 
// Where the Problem records are "active=false"
gr.addQuery('active', 'false');
 
// And the Incident records are "active=true"
grSQ.addCondition('active', 'true');
 
// Query
gr.query();
 
// Iterate and output results
while (gr.next()) {
    gs.info(gr.getValue('number'));
}

Find problems that have incidents associated where the incident caller_id field value matches that of the problem opened_by field.

var gr = new GlideRecord('problem'); 
gr.addJoinQuery('incident', 'opened_by', 'caller_id'); 
gr.query();