help constructing (what should be) an easy query.
-
Hi,
I have to create a query that is simple enough but i cant quite figure it out!
i have 3 tables needed in this query.
USERS
UserID (P.K)
UserName
THREADS
ThreadID (P.K)
ThreadName
ThreadDescription
UserID (person who created it)
POSTS
PostID (P.K)
ThreadID
UserID (person who created it)
DateEntered
What i need is a query to bring back the ThreadID, ThreadName, ThreadDescription, also the UserName of the person who made the last post for the thread and the DateEntered of that post.
So the query will display the details of the threads but also who last posted in the thread and when they did so.
If you have any questions just ask.
Many thanks,
Ben
edit: univeristy are forcing me to use MS Access too
so this may not be the place to ask this question! -
the query should for all threads.
thanks,
ben
-
Untested, but I think this should do the trick.
select t.ThreadID,
t.ThreadName,
t.ThreadDescription,
x.UserName,
x.maxDate
from (
-- latest post for each thread with name and date
select p.ThreadID, u.UserName, max(p.DateEntered) as maxDate
from posts p
join users u
on p.UserID = u.UserID
group by p.ThreadID, u.UserName
) x
join threads t
on t.ThreadID = x.ThreadID
/Kenneth
-
Not sure it will
This might
SELECT t.ThreadID, t.ThreadName, t.ThreadDescription, u.UserName, x.DateEntered
FROM [THREADS] t
INNER JOIN (SELECT t2.ThreadID, MAX(p.DateEntered) AS [DateEntered] FROM [THREADS] t2
INNER JOIN [POSTS] p2 ON p2.ThreadID = t2.ThreadID
GROUP BY t2.ThreadID) x
ON x.ThreadID = t.ThreadID
INNER JOIN [POSTS] p ON p.ThreadID = x.ThreadID AND p.DateEntered = x.DateEntered
INNER JOIN [USERS] u ON u.UserID = p.UserID
Note that you will get multiple rows per thread if any post for a thread has the same DateEntered
Far away is close at hand in the images of elsewhere.
Anon. -
im afraid i cant get either of these to work, access keeps throwing up errors.
I have uploaded the DB to here http://www.sendspace.com/file/6v9yov,
its only 20k so if someone could help me out that would be great!
thanks,
Ben
-
You might want to post this in the Access forum.
-
will do. thanks
-
No..?
Why not?
I think it will
(adding the assumption here that DateEntered is a datetime so that it's possible to single out the 'latest' date on a given day) <g>
/Kenneth
-
I get a syntax error in FROM clause (highlighting the last join)
for this code
select t.ThreadID,
t.ThreadName,
t.ThreadDescription,
x.UserName,
x.maxDate
from (
select p.ThreadID, u.UserName, max(p.DateEntered) as maxDate
from posts p
join users u
on p.UserID = u.UserID
group by p.ThreadID, u.UserName
) x
join threads t
on t.ThreadID = x.ThreadID
if i then change the last join to inner join i still get the same error message but nothing is higlighted. Its because its Access i think, id much prefer to use SQL Server but we arent allowed.
there is a link to the database above if that helps.
thanks,
Ben
-
SELECT THREADS.ThreadID, THREADS.ThreadName, USERS.UserName,
[a].DateCreated
FROM USERS INNER JOIN
(([SELECT THREADS.ThreadID, MAX(POSTS.DateCreated) as DateCreated
FROM THREADS INNER JOIN POSTS ON THREADS.ThreadID = POSTS.ThreadID
GROUP BY THREADS.ThreadID]. AS [a]
INNER JOIN THREADS ON [a].ThreadID = THREADS.ThreadID)
INNER JOIN POSTS ON ([a].DateCreated = POSTS.DateCreated)
AND ([a].ThreadID = POSTS.ThreadID)) ON USERS.UserID = POSTS.UserID;
Far away is close at hand in the images of elsewhere.
Anon. -
Coffee and fresh air can make wonders....
David, you were right. It struck me that my inner query picks out all the latest posts from each user within each thread, not the latest..
/Kenneth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply