Geek Answers Handbook

SQL: JOIN with nested queries

I am trying to perform this connection operation. Since I'm new to sql, I find problems understanding syntax and stuff.

What do you think is wrong with the following query:

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) ) 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) ) ON dbo.transaction_unrated.cst_id = dbo.transaction_rated.unrated_transaction_id
+4
source share
1 answer

You need to alias your views.

 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 also unusual (in my experience). Are you sure you want to? Usually you do an INNER JOIN that returns rows that match your criteria in both tables, or you let one table be the driver and do LEFT or RIGHT OUTER JOIN that return all rows driving the table, is there a match in another table. A FULL OUTER JOIN will return all rows in both tables, regardless of whether they match.

+8
source

More articles:


All Articles