Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

> SQL help

post Feb 25 2006, 10:25 AM, updated 16y ago

Getting Started
Junior Member
95 posts

Joined: Feb 2006
basically i have 4 columns in the table called Forum. There are TopicId, Subject, Date and RelTopicId. For every new thread and reply on the thread, the TopicId is unique, which means for every record saved has an unique ID. For every new thread the RelTopicId will set to 0 and for every reply on the topic will set to the TopicId of the thread. This is how it look like.

TopicId Subject Date RelTopicId
1 asp 2/12/2006 11:40:40AM 0 ( This is a new thread)
2 RE asp 3/12/2006 11:56:10 PM 1 (This is a reply on subject asp)
3 RE asp 3/12/2006 12:45:12 PM 1 (This is a reply on subject asp)

TopicId 1 and RelTopicId 0 is the parent, and all the RelTopicId 1 is the child to the Parent which is TopicId 1.

here is the sql statement to retrive data
select * from Forum where Date >='2006-2-12' and Date <='2006-2-13 and TopicId = "0"
this is to get the parent.

my question is how to retrive the parent and the child data
i have try this select * from Forum where Date >='2006-2-12' and Date <='2006-2-13 and TopicId = "0" and RelTopicId = TopicId.

i got no luck. any help appreciate thanks.
post Feb 25 2006, 10:40 AM

Getting Started
Junior Member
84 posts

Joined: Feb 2006
Urm, why not make 2 tables? One for master thread (say, MThread) and the other one is the child (CThread).

TopicId (primary key), subject, date, author etc etc..

CTopidId (primary key), TopicId (this one linked with the above as 1-to-many), subject, author etc etc..

Then you can use 'SELECT * FROM CThread LEFT JOIN MThread ON CThread.TopicId = MThread.TopicId WHERE Date>=#<date here># AND Date<=#<date here>#'. This way, you can separate a topic with all its replies in 2 separate tables. Easier also if you want to find all topics/replies posted by a user. Hope this helps.


Change to:
| Lo-Fi Version
0.0178sec    0.50    5 queries    GZIP Disabled
Time is now: 21st June 2021 - 05:54 PM