连接两个查询结果(having WHERE和IN子句+子查询)

Question

正在尝试重新创建以下问题(不包括IN子句和子查询)：

/* this is query 1 */    
Select A.column_1,B.keyValue from table1 as A, table2 as B where
A.someColumn = B.someColumn and B.someotherColumn = 10

/* query 1 gives */
column1 | keyValue
_________________

data-A1 | key-1 
data-A2 | key-2
data-A3 | key-3

/* this is query 2 */
Select AVG(ratings) as ratings, C.keyValue from table3 as C, 
table4 as D where C.someColumn = D.someColumn and D.someotherColumn = 'abc'

/* query 2 gives */
ratings | keyValue
_________________

rating-1 | key-1 
rating-2 | key-2
rating-3 | key-3

/* this is the desired result */

column1 | ratings | keyValue
_________________

data-A1 | rating-1 | key-1 
data-A2 | rating-2 | key-2
data-A3 | rating-3 | key-3

我用谷歌搜索了一下，发现mysql join是解决方案

SELECT table1.id, table2.column1, table1.column2 FROM table1 
INNER JOIN table2 ON table1.id = table2.id;

但这是一个非常基本的例子，只涉及两个表，我的第一个查询实际上涉及5个表，第二个查询涉及4个表，其中包含多个WHERE和IN子句+子查询。我不能用我的复杂查询实现这个连接逻辑。这是我尝试过的，但它在"JOIN“关键字后给我一个错误：

Select * from (Select A.column_1,B.keyValue from table1 as A, table2 as B 
where A.someColumn = B.someColumn and B.someotherColumn = 10) as first
JOIN
Select * from (Select AVG(ratings) as ratings, C.keyValue from table3 as C, 
table4 as D where C.someColumn = D.someColumn and D.someotherColumn = 'abc')
as second ON first.keyValue = second.keyValue;

任何帮助都将不胜感激。