How to reuse sub query result in sqlite

Question

When working on a mobile project with SQLite, I came across a query similar to the following code:

SELECT col_aa, col_ab FROM tbl_a
WHERE col_ac IN (SELECT col_ba FROM tbl_b WHERE col_bb IN (SELECT col_ca FROM tbl_c WHERE col_cb = 1))
AND col_ad IN (SELECT col_ba FROM tbl_b WHERE col_bb IN (SELECT col_ca FROM tbl_c WHERE col_cb = 1))

Obviously, these subqueries are redundant and wasteful.

After some research, it seems that CTE, a generic table expression, may be a solution, but this feature is not available in SQLite. (this way, I'm not sure if WITH temp AS statement will work or not)

Is there a better way to restructure a query to reuse the result from a subquery?

Answer 1

, tbl_b.col_ba ( ), JOIN. SQL:

SELECT 
  col_aa, 
  col_ab 
FROM 
  tbl_a JOIN
    (
      SELECT col_ba 
      FROM tbl_b 
      WHERE col_bb IN 
       (
         SELECT col_ca 
         FROM tbl_c 
         WHERE col_cb = 1
       )
    ) T2 ON (col_ac = T2.col_ba AND col_ad = T2.col_ba) 

,

Answer 2

, .... (, )

CREATE VIEW my_view AS 
SELECT col_ba FROM tbl_b WHERE col_bb IN (SELECT col_ca FROM tbl_c WHERE col_cb = 1)

SELECT col_aa, col_ab FROM tbl_a
WHERE col_ac IN (SELECT col_ba FROM my_view)
AND col_ad IN (SELECT col_ba FROM my_view)