SQL: JOIN with nested queries

join, sql

Solution

You need to alias your derived tables.

select top 1 * 
from 
(
     select * 
     from dbo.transaction_unrated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and content_provider_code_id in (1)
) rsQuery1  
FULL OUTER JOIN 
(
     select * 
     from dbo.transaction_rated 
        where transaction_date >= '2012/05/01' 
            and transaction_date < '2012/06/01' 
            and entity_id in (1) 
            and mapping_entity_id = 1)
) rsQuery2 ON rsQuery1.cst_id = rsQuery2.unrated_transaction_id

`FULL OUTER JOIN` is also unusual (in my experience). Are you sure that's what you want? Typically you will do an `INNER JOIN` which brings back rows that match on your criteria in both tables, or you will let one table be the driver and do a `LEFT` or `RIGHT OUTER JOIN` which will bring back all the rows in the driving table whether or not there is a match in the other table. A `FULL OUTER JOIN` will bring back all the rows in both tables regardless of whether they match.

Problem

Original source