MySQL LEFT JOIN来自不同数据库的2个查询

Question

我有2个独立的MySQL查询，每个查询使用不同的数据库，来自同一台服务器。

使用查询数据库1：使用DATABASE2

$result1 = mysqli_query($db_connection1, " 
SELECT p.id, p.identifier, u.star_total FROM pages AS p 
    LEFT JOIN ( 
     SELECT page_id, AVG(rating) star_total FROM (
      SELECT page_id, rating FROM comments WHERE comments.is_approved = '1' AND comments.rating != '0' 
      UNION ALL 
      SELECT page_id, rating FROM ratings) d 
      GROUP BY page_id 
    ) AS u ON p.id = u.id 
"); 

查询：

$result2 = mysqli_query($db_connection2, " 
SELECT 
    cas.id, cas.casino, 
    t2.amount, t2.match, 
    t3.bonus_id, t3.bonus_type 
FROM (SELECT * FROM casinos $query) AS cas 
LEFT JOIN ( 
     bonus AS t2 
    INNER JOIN 
     bonus_type_lookup AS t3 ON t2.bonus_type = t3.bonus_id 
) 
ON cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
AND (
    t2.amount = (
     SELECT 
      CASE WHEN MAX(t2.amount) > MAX(t2.match) OR (MAX(t2.match) IS NULL) THEN MAX(t2.amount) 
      ELSE MAX(t2.match) 
      END 
     FROM bonus AS t2 
     WHERE cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
    ) 
    OR t2.match = (
     SELECT 
      CASE WHEN MAX(t2.amount) > MAX(t2.match) 
      THEN MAX(t2.amount) 
      ELSE MAX(t2.match) 
      END 
     FROM bonus AS t2 
     WHERE cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
    ) 
) 
ORDER BY $order_by $order $limit 
"); 

两个查询自己工作的罚款。现在我想要LEFT JOIN这两个查询。我目前的尝试（见下文）产生这个错误：mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given，这referes这一行：$numrows = mysqli_num_rows($result);

$result = mysqli_query($db_connection2, " 
SELECT 
    cas.id, cas.casino, 
    t2.amount, t2.match, 
    t3.bonus_id, t3.bonus_type, 
    **j.identifier, j.star_total** 
FROM (SELECT * FROM database2.casinos $query) AS cas 
LEFT JOIN (
     database2.bonus AS t2 
    INNER JOIN 
     database2.bonus_type_lookup AS t3 ON t2.bonus_type = t3.bonus_id 
) 
ON cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
AND (
    t2.amount = (
     SELECT 
      CASE WHEN MAX(t2.amount) > MAX(t2.match) OR (MAX(t2.match) IS NULL) THEN MAX(t2.amount) 
      ELSE MAX(t2.match) 
      END 
     FROM database2.bonus AS t2 
     WHERE cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
    ) 
    OR t2.match = (
     SELECT 
      CASE WHEN MAX(t2.amount) > MAX(t2.match) 
      THEN MAX(t2.amount) 
      ELSE MAX(t2.match) 
      END 
     FROM database2.bonus AS t2 
     WHERE cas.id = t2.id AND t2.bonus_type = '$bonus_typeNo' 
    ) 
) 

LEFT JOIN (
    SELECT 
     p.id, 
     p.identifier, 
     q.star_total 
    FROM database1.pages p 
    LEFT JOIN ( 
     SELECT 
      t.page_id, 
      AVG(t.rating) star_total 
     FROM ( 
      SELECT 
       c.page_id AS page_id, 
       c.rating AS rating 
      FROM database1.comments c 
      WHERE c.is_approved = '1' AND c.rating != '0' 
      UNION ALL 
      SELECT 
       r.page_id AS page_id, 
       r.rating AS rating 
      FROM database1.ratings r 
     ) t 
     GROUP BY t.page_id 
    ) q ON q.page_id = p.id 
) **j ON cas.id = j.identifier** 

ORDER BY $order_by $order $limit 
"); 
$numrows = mysqli_num_rows($result); 

我不知道是否从左至右的错误使用JOIN出现错误或者如果这是一个数据库的问题，或两者。 “$ db_connection2”使用数据库2。不知道它在什么阶段连接到database1，以及它如何获取使其连接到database1的连接详细信息。我试图为每个表和数据库使用完全限定的名称，但没有成功。

任何帮助将是伟大的。先谢谢你！

Answer 1

你得到这个错误信息：

Every derived table must have its own alias

错误消息意味着使用子查询在FROM子句中要求你给子查询表别名：

SELECT ... FROM (...subquery...) AS t1 ... 

这是为了你可以引用子查询的列，如t1.column1。

它记录在这里： https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html

The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name.