join tables from different databases only adding most recent date SQL-CodePudding

I am joining lots of data to handle dynamic/complex querying. The following is just a part of what I'm doing. I found a solution, then I think I tweaked it and lost it.

Database Structure:

phpMyAdmin
|
|-oath
|    -users
|    -users_meta
|    -users_jobs
|
|-order
|    -orders
|    -carts
|    -driver_status

oauth.users

id | foo | baa
--------------
1  | x   | x 
2  | x   | x 
3  | x   | x 
4  | x   | x 
5  | x   | x

order.orders ( I know bad naming, I didn't create the database)

(customer_id = user.id)

id|order_date| customer_id| ray
-------------------------------
1 | 10/11/21 |     1      | x 
2 | 10/11/21 |     1      | x 
3 | 09/11/21 |     1      | x 
4 | 12/11/21 |     1      | x 
5 | 10/11/21 |     2      | x 
6 | 12/11/21 |     2      | x 
7 | 14/11/21 |     2      | x

I want to join only the most recent date

id | foo | baa |order_date| baa
--------------------------------
1  |  x  |  x  | 12/11/21 | x 
2  |  x  |  x  | 14/11/21 | x

This is my query but something went wrong. Apologize Sql Isn't my strong point, the results were multiple of the same users with different order dates were showing. If there were multiple of the most recent order_date then there would be multiple entries of that.

SELECT
    *
FROM
    oauth.users u
LEFT JOIN order.orders o
ON
    o.customer_id = u.id
LEFT JOIN(
    SELECT
        customer_id,
        MAX(order_date) order_date
    FROM order.orders o2
    GROUP BY
        customer_id
) SubQ
ON
    SubQ.customer_id = o.customer_id AND SubQ.order_date = o.order_date

I'm using phpMyAdmin, and I think it uses Mysql / mariasql

CodePudding user response：

You can do it like this

SELECT
    *
FROM oauth.users oa
LEFT JOIN (SELECT oo.customer_id, oo.order_date  FROM order.orders oo
INNER  JOIN (
    SELECT
        customer_id,
        MAX(`order_date`) order_date
    FROM order.orders o2
    GROUP BY
        customer_id
) oo2 ON oo.customer_id = oo2.customer_id AND oo2.order_date = oo.order_date) t1
ON
    t1.customer_id = oa.id

see example http://sqlfiddle.com/#!9/4c6bbca/12