How to reuse a table with UNION?

Question

I am trying to reuse a table in SQLite. My attempt is as follows:

SELECT
    Partials.e_sentence
FROM
        (SELECT 
            e_sentence, _id
        FROM 
            Pair 
                JOIN PairCategories
                    ON
                        _id=PairId AND CategoryId=53

        UNION

        SELECT 
            e_sentence, _id
        FROM 
            Pair
        WHERE
            e_sentence LIKE '%' || 'how often' || '%'
        GROUP BY 
            e_sentence)
    AS Parents JOIN Partials
        ON Parents._id=ParentId

UNION

SELECT
    e_sentence
FROM
    Parents

The key part I am trying to accomplish is at the bottom, where I try to UNION a table created in the previous statement. Is there a way to do this in SQLite, or am I forced to repeat the query that made the Parents table in the first half of the UNION?

score 3 · Accepted Answer · edited May 23 '17 at 11:51

In SQLite 3.8.3 or later, you can use a common table expression:

WITH Parents AS (
    SELECT e_sentence, _id
    FROM Pair
    JOIN PairCategories
    ...
)
SELECT Partials.e_sentence
FROM Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM Parents;

If you're using an older SQLite (probably because you're using an older Android), you can create a view for the subquery:

CREATE VIEW Parents AS
    SELECT e_sentence, _id
    FROM Pair
    JOIN PairCategories
    ...;

SELECT Partials.e_sentence
FROM Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM Parents;

If you do not want to have this view permanently in the database, you could make it temporary (CREATE TEMPORARY VIEW ...) so that it is not available outside the current database connection, or, as last resort, you could just insert the subquery wherever you would use Parent:

SELECT Partials.e_sentence
FROM (SELECT ...) AS Parents
JOIN Partials ON Parents._id = ParentId
UNION
SELECT e_sentence
FROM (SELECT ...) AS Parents;

This is exactly what I'm looking for! I wish I could award you more points as you've saved me a lot of headbanging. It's a shame StackOverflow's reward system favors simple and easy answers as opposed to well thought out and time-involved ones such as this :) — Daiwik Daarun, Jul 11 '15 at 18:19

How to reuse a table with UNION?

1 Answers1