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.
SQL help