加入2个表，从另一个表中获取不同的最新条目

Question

So I've started doing sales and slapped together a little setup to give me a bit of an advantage over my fellow sales people.

I have 2 databases - "clients" which has each client's particulars, and "clients_activity" which has entries that I add whenever I make contact with them.

All I need is a query that gets the latest activity from "clients_activity" for each unique user using the datetime field. What I've done gets the results but doesn't display the newest entry.. it looks like this:

SELECT
clients.client_name,
clients.id,
clients.client_id,
clients.institution,
clients.interested_in,

clients_activity.client_id,
clients_activity.contacted_on,
clients_activity.current_situation,
clients_activity.timestamp, 
clients_activity.contacted_by,
clients_activity.notes

FROM clients_activity

LEFT JOIN clients

ON clients.id = clients_activity.client_id

GROUP BY clients.client_name ORDER BY contacted_on DESC

It's of course somewhat insufficient and requires an extra little bit of wizardry, unfortunately my head is a bit numb at this point - any help would be appreciated!

Answer 1

Below query is what you are looking for. You need to use order by clause before joining the tables.

SELECT
    clients.client_name,
    clients.id,
    clients.client_id,
    clients.institution,
    clients.interested_in,
    c_activity.client_id,
    c_activity.contacted_on,
    c_activity.current_situation,
    c_activity.timestamp, 
    c_activity.contacted_by,
    c_activity.notes
FROM clients 
    LEFT JOIN (
        SELECT
            ca.client_id,
            ca.contacted_on,
            ca.current_situation,
            ca.timestamp,
            ca.contacted_by,
            ca.notes
        FROM clients_activity ca
        ORDER BY ca.contacted_on DESC
        ) c_activity
    ON clients.id = c_activity.client_id
GROUP BY clients.client_name;