CODEWITHSUNDEEP
mysqldatabasejoin
Jörg
Jörg

Reputation: 101

MySQL LEFT JOIN 2 queries from different databases

I have 2 separate MySQL queries, each working with a different database, from the same server.

query using database1: 

$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
");

query using database2:

$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
");

Both queries work fine by themselves. Now I want to LEFT JOIN those 2 queries. My current attempt (see below) produces this error: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given, which referes to this line: $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);

I am not sure whether the error arises from the wrong usage of LEFT JOIN or if this is a database problem, or both. "$db_connection2" uses database2. Not sure at what stage it connects to database1, and how it gets the connection details that enable it to connect to database1. I tried to use fully qualified names for each table and database, but no success.

Any help would be great. Thank you in advance!

Upvotes: 1

Views: 501

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

You got this error message:

Every derived table must have its own alias

The error message means that using a subquery in the FROM clause requires that you give the subquery a table alias: 

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

This is so you can reference columns of the subquery like t1.column1.

It's documented here: 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.

Upvotes: 1

Related Questions