How to select distinct records from two tables, combine into one column, and eliminate all records that exist in another table using SQLite?

Question

Here is my existing query which successfully selects distinct records from two tables and combines them into one column:

SELECT index_text AS unique_text FROM words
UNION
SELECT c1index_text FROM words_content 
ORDER BY unique_text

Now I want to eliminate all records WHERE body NOT IN (SELECT body FROM sms) (or NOT EXISTS, whatever works is fine). The problem is that no matter what I try, either I get a syntax error whenever I attempt to use parentheses, or it will not recognize sms.body (even if I precede every column by its parent table). I'm thinking some SQLite limitations may be making this harder than it needs to be, but there has to be a workaround. Below are queries I have tried unsuccessfully (I have also tried numerous variations of these queries to no avail):

SELECT index_text AS unique_text FROM words
UNION
SELECT c1index_text FROM words_content
  WHERE body NOT IN (SELECT body FROM sms)
ORDER BY unique_text

Results in error: No such column: body

SELECT words.index_text AS unique_text FROM words
UNION
SELECT words_content.c1index_text FROM words_content
LEFT JOIN sms
  ON sms.body=unique_text
ORDER BY unique_text

Results in error: No such column: unique_text

How do I join to an alias column and show only records that do not exist in sms.body? Thanks,

Answer 1

If you use where or join clauses in UNIONs you have to apply them to both select statements.

SELECT index_text AS unique_text 
FROM words
where index_text NOT IN (SELECT body FROM sms)
UNION
SELECT c1index_text 
FROM words_content
WHERE c1index_text NOT IN (SELECT body FROM sms)
ORDER BY unique_text