Postgresql – Using intermediate result in backend with node-postgres

node.jspostgresql

Firstly, I am using the node-postgres module to access database in a node application.

I have this SQL code that pulls up data from a table data of which is pulled from another table.

SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN 
(
    SELECT task_id
    FROM _tasks
    WHERE onetsoc_code IN
    (
    SELECT onetsoc_code
    FROM _alternate_titles
    WHERE alternate_title
    ILIKE '%Beta Tester%'
    )
)

I would like to use this query like client.query(query)(Please refer to the docs).
I would like to use the intermediate result (the table that is comprised of values of task_id) in a backend application. However I also need the end result, the table that is comprised of values of dwa_id in that backend application. I have a solution which is writing a separate query for each result, which seems inefficient. Is there another more efficient approach to this problem?

Best Answer

You will need to store the intermediate result in a temporary table, but there is no way to avoid the two queries:

create temporary table temp_result
as
SELECT task_id
FROM _tasks
WHERE onetsoc_code IN 
    (
    SELECT onetsoc_code 
    FROM _alternate_titles 
    WHERE alternate_title ILIKE '%Beta Tester%'
    );

Then, using the same (physical) connection to the database, you can run the query to retrieve the intermediate result:

select *
from temp_result;

And the actual result you want:

SELECT dwa_id
FROM _tasks2dwa
WHERE task_id IN (select task_id from temp_result);

If you only want (or get) each dwa_id only once, another option is to aggregate the task_ids into an array:

Something along the lines:

SELECT td.dwa_id, array_agg(t.task_id) as task_ids
FROM _tasks2dwa td
  JOIN _tasks t on td.task_id = t.task_id
WHERE t.onetsoc_code IN (SELECT alt.onetsoc_code
                         FROM _alternate_titles alt
                         WHERE alt.alternate_title ILIKE '%Beta Tester%'
GROUP BY td.dwa_id;
Related Question