need help on creating a 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.
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!
Many thanks,
Ben
-
Ben
Will this work? I haven't tested it.
John
select
ThreadID,
ThreadName,
ThreadDescription,
UserName as LastPoster,
max(DateEntered) as LastPostDate
from Threads t join Posts p on t.ThreadID = p.ThreadID
join Users u on p.UserID = u.UserID
group by ThreadID, ThreadName, ThreadDescription, UserName
-
i get an error saying syntax error in FROM clause (highlighting the first join).
if i change it to inner join i get this error message:
Syntax error (missing operator) in query expression 't.ThreadID = p.ThreadID join Users u on p.UserID = u.UserID'.
thanks,
Ben
-
Ben
Can't see what's wrong with it just by looking, and I can't access your database on the web. I'm sure someone will come to my rescue...
John
-
The query is fine but you need to identify where are you taking certain fields from. For example, "ThreadID" appears in THREADS and POSTS, so use something like
select
t.ThreadID,
t.ThreadName,
t.ThreadDescription,
u.UserName as LastPoster,
max(p.DateEntered) as LastPostDate
from Threads t left join Posts p on t.ThreadID = p.ThreadID
left join Users u on p.UserID = u.UserID
group by t.ThreadID, u.UserName, ThreadName, ThreadDescription
I changed the joins to OUTER LEFT JOINs so that you can browse all threads even if there are no posts in them.
-
Im am still getting this error...
http://img451.imageshack.us/img451/3696/error0ka.jpg
any more help is greatly appreciated as this is so frustrating.
Ben
P.S the database is here http://www.sendspace.com/file/6v9yov if this can help anyone
-
I setup the tables just like you specified and ran the query and it ran fine. What are the datatypes of the fields in each table? I just made some assumptions when I setup the test tables on my server.
-
Disregard my previous post, i just downloaded your database.
I messed with the query posted here and got this to run
select
Threads.ThreadID,
Threads.ThreadName,
Threads.Description,
Users.UserName as LastPoster,
max(Posts.DateCreated) as LastPostDate
from (Threads left outer join Posts on Threads.ThreadID = Posts.ThreadID)
left outer join Users on Posts.UserID = Users.UserID
group by Threads.ThreadID, Users.UserName, ThreadName, Threads.Description
Hopefully this works for you as well.
-
yeh that works great!!
one slight problem is that it displays entries for all the posts,
At the moment it shows.
ThreadID PostID LastPostDate
4 1 2/5/2006
4 2 3/5/2006
5 3 4/5/3006
so a thread is listed twice for both posts in it, where as i want just the last post to be displayed. i think i need to alter one of the joins but im not sure which one.
any ideas? thanks for the help
-
The problem is with the grouping, lets look at the sql I posted for a sec.
select
Threads.ThreadID,
Threads.ThreadName,
Threads.Description,
Users.UserName as LastPoster,
max(Posts.DateCreated) as LastPostDate
from (Threads left outer join Posts on Threads.ThreadID = Posts.ThreadID)
left outer join Users on Posts.UserID = Users.UserID
group by Threads.ThreadID, Users.UserName, ThreadName, Threads.Description
This will return all entries that have a different ThreadID, ThreadName, Description, and UserName and then return the Max of the Date of all records that match those 4 fields exactly.
If you're doing this in entirely in access like the db you posted, I would probably split this into 2 queries. 1 to return the last post, and then 1 to return the extra data. Something like this
(Query is called qryLastPostPerThread)
SELECT Threads.ThreadID, Threads.ThreadName, Threads.Description, Max(Posts.DateCreated) AS LastPostDate, Max(Posts.PostID) AS MaxOfPostID
FROM Threads LEFT JOIN Posts ON Threads.ThreadID = Posts.ThreadID
GROUP BY Threads.ThreadID, Threads.ThreadName, Threads.Description;
(The Query that brings it all together)
SELECT qryLastPostPerThread.*, USERS.UserName AS LastPoster
FROM qryLastPostPerThread LEFT JOIN (USERS RIGHT JOIN POSTS ON USERS.UserID = POSTS.UserID) ON qryLastPostPerThread.MaxOfPostID = POSTS.PostID;
Feel free to change up the names of the queries, but that should give you what you are looking for.
This also assumes that the Last Post on any thread will have a higher PostID than any other posts on that thread.
Hope that helps.
-
thanks for the queries. think ill leave this task, it seems a little too hard as i was trying to combine both scripts into one so i could use it in a single datagrid.
thanks,
ben
-
If you want to get it all done with one block of SQL, use this:
SELECT
[ThreadID] ,[PostiD] ,[UserID] ,[DateEntered]
FROM [ScratchPad].[dbo].[Posts] pmain
where pmain.DateEntered =
(
SELECT max (psub.[DateEntered]) LatestPostDateTime
FROM [ScratchPad].[dbo].[Posts] psub
Where psub.ThreadID = pmain.ThreadID
)
order by pmain.threadid
(The order by was just so I could check the data in the output. You probably don't want that.)
Frankly, although this kind of "sexy" SQL is a lot of fun, there really is a better way to get what you want, thinking ahead to having lots and lots of data, which I'm sure you hope to have.
Learn about triggers. In your threads table, add a column to indicate "this row is the latest post to this thread". It should be boolean, with a default of True.
Now, by default, every new row is the latest post, right? How do you get rid of the mark on the previous post? Simple. Look at the SQL below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.ClearPreviousLatestInThread
ON dbo.Posts
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Update Posts
Set LatestPostInThread = 0
Where ThreadID = (Select ThreadID From inserted)
And PostID <> (Select PostID from inserted)
And LatestPostInThread = 1
END
GO
Why do this? It's a way to speed up the data selection later. Using the Max(DateEntered) approach is "clean" from one perspective: you don't have to update a static column to keep track of which post is most recent in the thread. But it's also a performance hog when you have kajillion rows.
By farming out the server usage to clear only one row while it's adding the new row, you make it possible to use this SQL to get what you want without using MAX.
SELECT [ThreadID] ,[PostiD] ,[UserID] ,[DateEntered]
FROM [ScratchPad].[dbo].[Posts] pmain
where LatestPostInThread = 1
Done.
I wouldn't call one more correct than the other. They're just two different ways of attacking the same problem. One stores less data (using Max()). One processes less data (using bit column for immediate selection).
LOL
-
I overlooked a fairly important detail: The SQL I gave you for selecting the latest post (either one) will work just fine in an Access query, but the trigger stuff is ONLY for SQL Server.
You do not have triggers in Access. However, if you understand what the trigger SQL is accomplishing, you can use VB code within your Access form to do exactly the same thing. You'd use an ADO connection and command instead of a table trigger. The SQL for the command would like about like this:
cmd.sql = "Update Posts Set LatestPostInThread = 0 Where ThreadID = " & Me.txtThreadID _
& " And PostID <> " & lngNewPostID & " And LatestPostInThread = 1"
You would have to have a textbox with the current ThreadID on the form that's adding the post. You would also have to retrieve the new post's PostID into lngNewPostID before trying to run the SQL.
Still recognizably the same task if you squint your eyes and ignore the distractions of terminology.
HTH.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply