SQLite - Combining Two Subqueries

Question

Or at least I think they are called subqueries (new to and self-taught in SQLite). I have two statements SELECTfrom two tables in one database. I would like to join these two subqueries along the dateand columns symbol. Subqueries work fine, but when I try JOIN, I get an error ( error in statement: near "JOIN": syntax error). Here is my query string:

SELECT date, symbol, SUM(oi*contract_settle) AS oi_dollar
    FROM (SELECT date, symbol, oi, contract_settle
            FROM ann
            UNION
            SELECT date, symbol, oi, contract_settle
            FROM qtr) 
    GROUP BY date, symbol
    HAVING oi_dollar > 0
    JOIN
    (SELECT date, symbol, ret FROM crsp
    USING (date, symbol))

Thank!

Answer 1

JOIN GROUP BY. , , sqlite "" , :

SELECT a.date, a.symbol, SUM(a.oi * a.contract_settle) AS oi_dollar
FROM (SELECT date, symbol, oi, contract_settle
        FROM ann
        UNION
        SELECT date, symbol, oi, contract_settle
        FROM qtr) a
INNER JOIN crsp c ON a.date = c.date AND a.symbol = c.symbol
WHERE a.oi * a.contract_settle > 0
GROUP BY a.date, a.symbol

oi contract_settle (, ), WHERE a.oi <> 0 AND a.contract_settle <> 0 .

Answer 2

, on. SQLite, SQL ( ):

SELECT date, symbol, SUM(oi*contract_settle) AS oi_dollar
    FROM (SELECT date, symbol, oi, contract_settle
            FROM ann
            UNION
            SELECT date, symbol, oi, contract_settle
            FROM qtr) as 'a'
    JOIN
    (SELECT date, symbol, ret FROM crsp
    USING (date, symbol)) as 'b'
    ON a.date = b.date AND a.symbol = b.symbol
    GROUP BY date, symbol
    HAVING oi_dollar > 0