Join the results "generated" from two SELECT statements with different schemas into one table

Question

So, I have the following SELECT statements:

SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4) AS Decade FROM Movies M, Awards A WHERE {SOME WHERE CLAUSE} GROUP BY Decade; 

and

 SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade FROM Awards A2 WHERE {SOME WHERE CLAUSE} GROUP BY Decade; 

The first is the "generation" of a table with columns (US, Decade), and the second is the "generation" of another table with columns (Total, Decade). I want to join these two tables to get a table (US, Total, Decade). How can i do this?

Answer 1

Put them in subqueries and execute JOIN :

 SELECT a.US, a.decade, b.total FROM (SELECT COUNT(A.Award) AS US, SUBSTRING(CAST(M.Year as char(4)), 0 , 4) AS Decade FROM Movies M, Awards A WHERE {SOME WHERE CLAUSE} GROUP BY Decade ) AS a INNER JOIN (SELECT COUNT(*) AS Total, SUBSTRING(CAST(A2.Year as char(4)), 0 , 4) AS Decade FROM Awards A2 WHERE {SOME WHERE CLAUSE} GROUP BY Decade) AS b ON a.decade = b.decade