Subqueries

PQL supports the use of subqueries to include unrelated data in a single query. You can nest one query inside of another query to search for several unrelated items at once. You can use subqueries in both the projection (SELECT clause) and the predicate (WHERE clause). For example, instead of combining items by listing them like this: SELECT 'x', 'y' FROM /, you can combine things by embedding several subqueries within a single main query, like this:

SELECT (SELECT 'x' FROM /), (select 'y' FROM /) FROM /

This returns the following results:

row 
  select x from / 
     x: x 
  select y from / 
     y: y 

Note: Subqueries take awhile to execute, so be prepared to wait a few minutes for the results. If another, more efficient method exists, you're better off using it. Subqueries are supported, however, for circumstances where there is no other method.

Examples

You can try the following examples against your own data in the GoToAssist search field, or against fictitious data in the Query Sandbox:

• Example 1: Find the number of computers
• Example 2: Find the percentage of servers
• Example 3: Find X where A is a subquery
• Example 4: Find uncorrelated data

Example 1: Find the number of computers

Subqueries can be useful for computing percentages. For example, here is the simple query that finds the number of computers on the network:

Query: How many computers do I have?

SELECT count(*) FROM /network/device 
WHERE system/computed_class='server'

Results: Depending on your database, the result will be something like this:

row 
   count(*): 13  

Example 2: Find the percentage of servers

You can embed a subquery in the SELECT clause to extend Example 1, to find the percentage of computers that are servers:

Query: What percent of computers are servers?

SELECT count(*) / (SELECT count(*) FROM /network/device) 
FROM /network/device 
WHERE system/computed_class='server'

Results: Depending on your database, the result will be something like this:

row 
 count(*)/select count(*)from /network/device: 0.0718232  

Example 3: Find Find X where A is a subquery

You can embed a subquery in the WHERE clause to look up something in one place and find it in another place, like this:

Query: Find x where a is a subquery.

SELECT 'x' FROM / WHERE 'a' = (SELECT 'a' FROM /)

This query as.

Results:

row 
   x: x   

Example 4: Find uncorrelated data

You can only use this with uncorrelated subqueries that don't depend on the main query:

Query: Find uncorrelated data.

SELECT 'x' FROM / WHERE 'a' = (SELECT 'b' FROM /)

Results:

row 
   x: x  

Related topics

• Functions
• Operators
• Expressions
• Read about PQL

| Views: 984 | Last Updated: Thu, May 09 2013 2:28 PM

Is this article helpful?