show all user records except max date

user_15915607 0
Viewed 24

Hey guys I know there were many questions asked similar to my problem but I couldn't find the exact one to solve mine.

I have two tables in the database one containing user id, name, and if its an active user the second table is with the id and date when he posted something.

example:

db_user (id, name, is_active)

db_post (user_id, udate)

I needed to see all records that are not (not MAX(date) ) the latest records.

I tried:

SELECT name, x.user_id, x.udate FROM db_post x
LEFT JOIN deb_user y
ON x.user_id = y.id

WHERE is_active=1 AND  x.date not in (SELECT t.udate FROM
(SELECT id, MAX(udate) AS udate FROM db_post
LEFT JOIN db_user e
ON id = e.id
GROUP BY ID)t)

ORDER BY id, udate

It works fine until some user's Max Date is others non-Max and it does not show all of these records. Any ideas on how to fix my code?

mysql sql database
1 Answers

If you want all posts that are not the latest, you can use a correlated subquery:

select p.*
from db_post p
where p.udate < (select max(p2.udate)
                 from db_post p2
                 where p2.user_id = p.user_id
                );

Based on your question, I don't understand why the db_user table would be needed. However, if you want to filter on the status, you can join that in.

user_11440350
Related