title: SQLite FTS4 with preferred language
body: <p>I have an SQLite table that was generated by using the FTS4 module. Each entry is listed at least twice with different languages, but still sharing a unique ID (int column, not indexed). 
  Here is what I want to do:
  I want to lookup a term in a preferred language. I want to union the result with a lookup for the same term using another language.
  For the second lookup though, I want to ignore all entries (identified by their ID) that I already found during the first lookup. So basically I want to do this:</p>
  
  <pre><code>WITH term_search1 AS (
      SELECT *
      FROM myFts
      WHERE myFts MATCH 'term'
      AND languageId = 1)
  SELECT *
  FROM term_search1
  UNION
  SELECT *
  FROM myFts
  WHERE myFts MATCH 'term'
  AND languageId = 2
  AND id NOT IN (SELECT id FROM term_search1)
  </code></pre>
  
  <p>The problem here is, that the term_seach1 Query would be executed twice. Is there a way of materializing my results maybe? Any solution for limiting it to 2 Queries (instead of 3) would be great.</p>
  
  <p>I also tried using recursive Queries, something like:</p>
  
  <pre><code>WITH RECURSIVE term_search1 AS (
      SELECT *
      FROM myFts
      WHERE myFts MATCH 'term'
      AND languageId = 1
  UNION ALL
      SELECT m.*
      FROM myFts m LEFT OUTER JOIN term_search1 t ON (m.id = t.id)
      WHERE myFts MATCH 'term'
      AND m.languageId = 2
      AND t.id IS NULL
  )
  SELECT * FROM term_search1
  </code></pre>
  
  <p>This didn't work neither. Apparently he just executed two lookups for languageId = 2 (is this a bug maybe?).</p>
  
  <p>Thanks in advance :)</p>