除了左连接和联合mysql查询之外，还从两个表中选择最新的条目

Question

I have three tables that record conversation activity and one table containing user info

I am trying to put a query together that summarises a users conversations (like an inbox with the last comment visible)

my tables are set up like the below

users
---
company | contact_person | pic_small

alerts
---
comment_id | user_id | poster_id | timestamp

activity
---
comment_id | user_id | comment | timestamp

comments
---
comment_id | user_id | comment | timestamp

When a user initially makes contact, the timestamp, comment_id and id's of the users (person sending and person receiving) get inserted into the alerts table. The user_id, comment_id, timestamp and actual comment also get inserted into the activity table.

Then once the conversation has started, all comments (comment_id, user_id,comment, timestamp) get inserted into the comments table.

As mentioned above, what I am trying to do is summarise a users activity so it looks like their inbox.

I have come up with the below query that gives me the user id's, comment id's and user details of all the conversation activity received or sent by logged in user.

            SELECT alerts.comment_id,
                   alerts.user_id,
                   alerts.poster_id,
                   alerts.timestamp,
                   users.contact_person,
                   users.company,
                   users.pic_small
            FROM alerts
            LEFT JOIN users ON users.user_id = alerts.user_id
            WHERE alerts.user_id = %s
            GROUP BY alerts.comment_id
            UNION
            SELECT alerts.comment_id,
                   alerts.user_id,
                   alerts.poster_id,
                   alerts.timestamp,
                   users.contact_person,
                   users.company,
                   users.pic_small
            FROM alerts
            LEFT JOIN users ON users.user_id = alerts.poster_id
            WHERE alerts.user_id = %s
            GROUP BY alerts.comment_id
            ORDER BY TIMESTAMP DESC

The part I am stuck on is getting the last comment (comment with newest timestamp) from either the activity or comments table (It could be either). Happy to change the above query completely if needed.

Below is what I am trying to achieve, I only want to see the user details of people that have contacted me or who I have contacted - not my own details in my inbox (I can do this with php if needed) - It doesn't matter if the last comment was mine however.

Answer 1

Still it is not clear to me why you are using union.

You can get latest comment by below query, if you want to join some other results of other user_id then you can union these results in same way or let me know if you need different results.

SELECT 
comment_id,comment, user_id,poster_id,TIMESTAMP, contact_person,company,pic_small 
FROM 
(SELECT 
alerts.comment_id,alerts.user_id,alerts.poster_id,alerts.timestamp,users.contact_person,users.company,users.pic_small,act.comment 
FROM alerts 
JOIN activity act 
ON act.comment_id=alerts.comment_id 
LEFT JOIN users ON users.user_id = alerts.user_id 
WHERE alerts.user_id = %s 
ORDER BY act.timestamp DESC) a 
GROUP BY comment_id;