SQLite: How to Use Subqueries

This post shows a query with two subqueries in SQLite. The corresponding database contains study units and specific process information, including what, who, and when. In Bash, you can use the pipe operator (|) to execute a query in SQLite and send the stderr (2) and stdout (1) to a file:

$QUERY is the SQL statement:

This particular query does the following:

  • Selects the newest post from a table with process information
  • Adds the meaning to the process code
  • Adds the current process time (excluding some processes in the calculation)
  • Selects the newest expected process time from another table and subtracts the current process time
  • Adds comments from another table

All selections are grouped by the study unit (if necessary), whereas the current process time is multiplied by ~0,6 to get working days. The first select statement ultimately excludes some processes in a WHERE clause.

Note: The SQL example uses aliases and subqueries which reduce the readability of the code. I would not recommend using it in production code.

 

Leave a Reply

Your email address will not be published.