Gossamer Forum
Home : General : Databases and SQL :

here goes sql statement

Quote Reply
here goes sql statement
Hi I'm making a forum, and I have a problem with an sql statement.

I don't know if I can show it right but it's worth a shot. Here's a simplified version

MyPostTable:
PID
PostSubject
DatePosted
UserID

MyForumTable:
FID
ForumName

MyDicussionTable:
DID
ForumID (Linked To FID of my forum)
PostID(linked to the PID in MyPostTable)
LastPostID(Also Linked to the PID in MyPostTable)

MyUserTable:
UID
Username

Now here's what I'm looking for. I want to select my Discussions and Return
  1. PostSubject
  2. Username of The user who started the discussion
  3. Username of the user who last posted in the discussion
  4. DatePosted of the user who last posted in the discussion


My problem is that I want 2 different usernames right, and I'm linking from myDiscussion table through MyPOst table to my usertable.
So how do I return 2 different username in one query and attach the datePosted to the User who last posted.

This is an issue I've run into many time but could never find a good SQL statement and end up always having to redidesign my table. SO I would be forever grateful to whomever would take time to help me.

Thanks a Million in advance.



Dan
Quote Reply
Re: [dan2003] here goes sql statement In reply to
OK,

Here's a simpler way of putting it.

TblUsers
USERID
USERNAME

TBLDiscussions
DiscussionID
FirstPostBY (The USERID of The First Poster)
LastPostBY (The USERID of The Last Poster)

So How do I link the two tables with 2 Fields (FirstPostBY, LastPostBY), and how will the statement diferenciate the 2 users?

Please Help.

Thanks
Quote Reply
Re: [dan2003] here goes sql statement In reply to
One quick solution to your problem is using a "recursive" structure in your POSTS table.

POSTS:
PID
ParentPID
UserID

Basically, when someone new replies to a post, the original first PID should be inserted into the ParentPID column.

Example of how this would look:

PID = 1000
ParentPID = NULL (or 0)
UserID = 100

PID=1001
ParentPID = 1000
UserID = 99

Then all you have to do to show the original answer (first), is to use PARENTPID IS NULL.

And then order the PARENTPID thereafter...
========================================
Buh Bye!

Cheers,
Me

Last edited by:

Stealth: Feb 10, 2003, 9:17 AM