MySQL - Join 2 tables

Question

I have 2 tables: users & balance.

I want to join the tables with all of the details from the user table (all fields of all tuples) with the most recent entry from the balance table (1 field linked by a user id).

Here is the structure of the tables:

balance:

+---------+
| Field   |
+---------+
| dbid    |
| userId  |
| date    |
| balance |
+---------+

users:

+-------------+
| Field       |
+-------------+
| dbid        |
| id          |
| fName       |
| sName       |
| schedName   |
| flexiLeave  |
| clockStatus |
+-------------+

I have been trying for hours to do this and the closest I can get is to return a row for a single user:

SELECT u.*, b.balance, b.date 
FROM users u, balance b 
WHERE
    u.id = b.userId AND
    b.date = (SELECT MAX(date) FROM balance WHERE userId = 'A8126982');

Or I can select all users but not the most recent entry in the balance table:

SELECT u.*, b.balance, b.date 
FROM users u, balance b 
WHERE u.id = b.userId GROUP BY u.id;

I have tried many different queries and seem to be getting closer but I just can't get to where I want to be.

Any help would be appreciated.

Answer 1

You can use the first SQL you wrote but for all users:

SELECT u.*, b.balance, b.date
FROM users u JOIN balance b ON u.id = b.userId
WHERE b.date = (SELECT MAX(date) FROM balance WHERE userId = u.id);

This may not be the fastest way to get the result, but it'll give you what you need. I use similar queries in quite a few places in my app.