CODEWITHSUNDEEP
phpmysqljoin
Sunfire
Sunfire

Reputation: 35

Joining 2 tables and getting distinct, newest entry from the other

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!

Upvotes: 0

Views: 36

Answers (3)

rohit hiresheddi
rohit hiresheddi

Reputation: 100

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;

Upvotes: 0

Bill Rose
Bill Rose

Reputation: 111

Assuming your implementation supports common table expressions,

WITH MaxDate_CTE AS
(
    SELECT
        client_id,
        MAX(timestamp) AS MaxTime
    FROM
        clients_activity
    GROUP BY
         client_id
)
SELECT
    a.*
FROM
    clients_activity a
        INNER JOIN MaxDate_CTE m ON a.client_id = m.client_id AND a.timestamp = m.MaxTime
ORDER BY
    a.contacted_on DESC;

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You need to first get the latest activities and then join back to clients table. Assuming timestamp in the clients_activity table represents the latest or oldest you may use the following

select
c.client_name,
c.id,
c.client_id,
c.institution,
c.interested_in,
ca.client_id as ca_client_id,
ca.contacted_on,
ca.current_situation,
ca.timestamp, 
ca.contacted_by,
ca.notes
from clients c 
join clients_activity ca on ca.client_id = c.client_id
join(
 select max(timestamp) as timestamp,client_id
 from clients_activity
 group by client_id
)t
on t.client_id= ca.client_id and t.timestamp = ca.timestamp

Upvotes: 3

Related Questions