JOIN on MAX value 1

[emozley]

Hi,

I am creating a threaded email database. I have three tables - Threads, Messages and Users.

The objective is to show all threads with unread messages and who the last message was posted by for each thread.

For this example the database knows I am logged in with UserID of 1. My Messages table has a field called AuthorID and my Users table has a field called UserID, FirstName and Surname.

What I am hoping to do is once I've got the maximum MessageID value is to be able to retrieve the AuthorID value and then do a join with the Users table on that value.

Is it possible to do this all in a single query?

Thanks very much

Ed


SELECT "M" AS Category, ThreadID, Title,

(SELECT COUNT(*)
FROM Messages
WHERE Messages.ThreadID=Threads.ThreadID AND
Messages.NotReadBy LIKE '%;1;%') AS NewMessages,

(SELECT MAX(MessageID)
FROM Messages
WHERE ThreadID=Threads.ThreadID) AS LatestMessageID

FROM Threads
WHERE Threads.Recipients LIKE '%;1;%';

 

[lespaul]

maybe this:

SELECT * FROM Users U
INNER JOIN (select Max(messageID), Authorid from Messages) B on B.Authorid = U.UserID

Leslie

Come join me at New Mexico Linux Fest!

 

[emozley]

That didn't work - after a lot of fiddling around I was able to get it working without aliasing:

SELECT Threads.ThreadID, Threads.Title, Messages.AuthorID, Users.FirstName, Users.Surname,

(SELECT COUNT(*)
FROM Messages
WHERE Messages.ThreadID=Threads.ThreadID AND
Messages.NotReadBy LIKE '%;1;%') AS NewMessages

FROM (Threads
LEFT JOIN Messages ON Threads.ThreadID=Messages.ThreadID) LEFT JOIN Users ON Messages.AuthorID=Users.UserID

WHERE Messages.MessageID=(SELECT MAX(MessageID) FROM Messages WHERE Messages.ThreadID=Threads.ThreadID)
AND Threads.Recipients LIKE '%;1;%';

 

[lespaul]

the query i posted doesn't do this:

What I am hoping to do is once I've got the maximum MessageID value is to be able to retrieve the AuthorID value and then do a join with the Users table on that value.

Is it possible to do this all in a single query?

it gets the max messageid for each user and then joins into the user table to get that information...you'd have to add some kind of where clause to it to get a specific user, but I'm pretty sure that query does exactly what you asked for.


Leslie

Come join me at New Mexico Linux Fest!

 

[emozley]

Hi Lesley,

Thanks for this - I've retried your code with the amendments you suggested and it does the trick. Aliasing might well come in handy as the query gets more complex and I try to return more information.

Thanks again

Ed

 

[lespaul]

glad it worked!

thanks for the star too!



Leslie

Come join me at New Mexico Linux Fest!