How to reuse a table with UNION?
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_sentenceFROM ParentsJOIN Partials ON Parents._id = ParentIdUNIONSELECT e_sentenceFROM 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_sentenceFROM ParentsJOIN Partials ON Parents._id = ParentIdUNIONSELECT e_sentenceFROM 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_sentenceFROM (SELECT ...) AS ParentsJOIN Partials ON Parents._id = ParentIdUNIONSELECT e_sentenceFROM (SELECT ...) AS Parents;