Title
#general
Mystery Incorporated

Mystery Incorporated

11/15/2021, 11:14 PM
Hi, is it possible to return a value with one query, and then use that value returned in another query, all in the same query string?
zwass

zwass

11/15/2021, 11:16 PM
Maybe subquery is what you're looking for? https://www.sqlitetutorial.net/sqlite-subquery/
f

fritz

11/16/2021, 12:11 AM
@zwassā€™s suggestion is great, subqueries can be an awesome way to return arbitrary data from disparate sources, for example:
SELECT 
  CONCAT(name,' ',version) AS operating_system, 
  (SELECT bundle_short_version 
   FROM apps 
   WHERE bundle_identifier = 'com.google.Chrome') AS chrome_version 
FROM os_version;
+------------------+----------------+
| operating_system | chrome_version |
+------------------+----------------+
| macOS 11.6       | 95.0.4638.69   |
+------------------+----------------+
12:23 AM
You can also use Common Table Expressions to create temporary tables which you can query from / join against. This is typically my preferred approach when I want to take one bit of data, then manipulate it in incremental steps:
WITH
macos_admin_users AS (
  SELECT 
    username, 
    uid 
  FROM users, user_groups USING(uid) 
  WHERE user_groups.gid = 80)
SELECT 
  username, 
  uid,
  datetime(password_last_set_time, 'unixepoch') AS password_last_set
FROM account_policy_data, macos_admin_users USING (uid);

+-------------+-----+---------------------+
| username    | uid | password_last_set   |
+-------------+-----+---------------------+
| fritz       | 502 | 2021-07-10 14:28:15 |
| kolide-imac | 501 | 2021-06-18 13:18:33 |
| root        | 0   |                     |
+-------------+-----+---------------------+
12:24 AM
In the above example I am looking for any user accounts which belong to group 80 (the macOS admin group) and then I am joining against that output to query the
account_policy_data
table which contains details like password set time.
12:25 AM
Most of the time you can accomplish similar outcomes with less verbose approaches that rely simply on clever joins, but CTEs can be a great way to compartmentalize your query.
Mystery Incorporated

Mystery Incorporated

11/17/2021, 6:54 AM
@fritz @zwass awesome as always thanks gents.