title: How to specify columns in an SQLite subquery join
body: <p>I have the following SQL Server query that I want to port over to SQLite 3, but it doesn't like it and complains of a syntax error "near (".</p>
  
  <p>After some debugging it appears that the problem is that SQLite doesn't like the column specifiers after the sub query alias (I have indicated these with a double asterisk).</p>
  
  <p>What do I need to do to make this query work with SQLite?</p>
  
  <pre><code>    SELECT  rowSet.DBId_0,
          rowSet.TableID_0, 
          CASE WHEN rowSet.HasIdentity_0 != rowSet.HasIdentity_1 THEN 1 ELSE 0 END 'Result'
  FROM (
      SELECT * 
      FROM
      (
          SELECT 'Master' AS 'Db', z.[DBId], z.[TableId], MAX(z.[MaxIdentity]) AS 'HasIdentity' 
          FROM
          (
              SELECT so.[DBId], sc.[TableId], MAX(CAST(sc.[Identity] AS TINYINT)) AS MaxIdentity
              FROM syscolumns sc
                  JOIN sysobjects so ON sc.[tableid] = so.[id]
              WHERE so.[dbid] = 1
              GROUP BY so.[DBId], sc.[TableId], sc.[Identity]
          ) z
          GROUP BY z.[DBId], z.TableId
      ) **src (Db_0, DBId_0, TableID_0, HasIdentity_0)**
      JOIN 
      (
          SELECT 'Target' AS 'Db', z.[DBId], z.[TableId], MAX(z.[MaxIdentity]) AS 'HasIdentity' 
          FROM
          (
              SELECT so.[DBId], sc.[TableId], MAX(CAST(sc.[Identity] AS TINYINT)) AS MaxIdentity
              FROM target.syscolumns sc
                  JOIN target.sysobjects so ON sc.[tableid] = so.[id]
              WHERE so.[dbid] = 1
              GROUP BY so.[DBId], sc.[TableId], sc.[Identity]
          ) z
          GROUP BY z.[DBId], z.TableId
      ) **tar (Db_1, DBId_1, TableID_1, HasIdentity_1)** ON tar.[DBId_1] = src.[DBId_0] AND tar.[TableId_1] = src.[TableId_0]
  ) rowSet
  </code></pre>
  
  <p>Here is a sample query you can try:</p>
  
  <p>This works (in SQLite):</p>
  
  <pre><code>select col1, col2
  from
  (
      select 1 as 'col1', 2 as 'col2'
      union
      select 3 as 'col1', 4 as 'col2'
  )
  </code></pre>
  
  <p>This doesn't (in SQLite):</p>
  
  <pre><code>select col1, col2
  from
  (
      select 1 as 'col1', 2 as 'col2'
      union
      select 3 as 'col1', 4 as 'col2'
  ) z (col1, col2)
  </code></pre>