Need help with a JOIN

Anon

I have 2 tables, 'users' and 'files' (defined below)

Can I construct a single SELECT statement so that it will return ONLY users that have files and ONLY the last file that the user has? So it would return 1 row per user containing their most recent file.

users

Id int
Name varchar

Files

Id int
Userid int
Filename varchar
Stamp int (unix_timestamp)

The way I'm doing it now, I use SELECT DISTINCT to get all the users with files, then a separate query for each user returned to retrieve their latest file. This method requires many, many individual queries to the database?

Any help is appreciated.

Thanks,
Mikey

Anon

Forgot to mention, this is mySQL.

Mikey

van__

$stmt = "SELECT * FROM files,users WHERE files.Filename != '' and users.Id = files.Id GROUP BY users.ID ORDER BY files.Stamp DESC"

This will return a result of all users with files with each users files ordered by your timestamp.

You'll have to filter your list in PHP,
simply loop through your array by user Id and take only the first line for each user IDs' results.

gyoung

You can do this, but not with a single SQL statement:

/ create a temporary table /

drop table if exists temp;

create temporary table temp(
Userid int,
Stamp int
);

/ insert user id and most recent timestamp /

insert into temp
select userid, max(Stamp)
from Files
group by userid;

/ ...and finally, what you want! /

select u.Id, Name, Filename
from ((users u
inner join Files f on u.Id = f.Userid)
inner join temp t on t.Userid = f.userid
and t.Stamp = f.Stamp);

A bit long-winded, I know.

Anon

You were right to try to accomplish this in one query - the HAVING clause basically runs a subquery on the results and keeps those that meet the condition.

SELECT u.id, u.name, f.filename, f.stamp, max(f.stamp) AS latest FROM users u INNER JOIN files f ON (u.id = f.userid) GROUP BY u.id HAVING f.stamp = latest;