CODEWITHSUNDEEP
phpmysql
CyberJunkie
CyberJunkie

Reputation: 22674

Mysql select oldest record for each user

I'm trying to create a list in PHP of the oldest entries for each user in the database.

SELECT *, 
MIN(`entries`.`entry_date`) 
AS entry_date 
FROM (`entries`) 
JOIN `user_profiles` 
ON `user_profiles`.`user_id` = `entries`.`user_id` 
WHERE `status` = 1 
GROUP BY `entries`.`user_id`

I'm using the query to retrieve from the entries table the oldest dated entry using MIN()and joining with table user_profiles for other data. The query should select the oldest entry for each user. It seems to work but it retrieves the wrong entry_date field on some entries when I echo them. Please help, I can't spot what I'm doing wrong..

Upvotes: 1

Views: 4201

Answers (2)

eggyal
eggyal

Reputation: 125835

You need to use a subquery to obtain the (user_id, entry_date) pairs for each user, then join that with a query that selects the records of interest:

SELECT *
FROM   entries
  NATURAL JOIN (
    SELECT   user_id, MIN(entry_date) AS entry_date
    FROM     entries
    GROUP BY user_id
  ) AS tmin
  JOIN user_profiles USING (user_id)
WHERE  status = 1

Upvotes: 4

Andy Jones
Andy Jones

Reputation: 6275

Have you tried approaching the problem from the user_profiles table instead of the entries table? If a user has no entries, they will not appear in the above query.

This may help, but I'm not sure if it's the full solution:

SELECT *, MIN(entries.entry_date) as entry_date
FROM user_profiles LEFT JOIN entries USING (user_id)
WHERE status = 1
GROUP BY user_profiles.user_id

Also, you're renaming the MIN(entires.entry_date) as entry_date... but you already have a column named entry_date. Try renaming the derived columns to something unique like "min_entry_date"?

Upvotes: 0

Related Questions