Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
doublek
Starting Member
8 Posts |
 Posted - 2006-02-24 : 21:42:15
|
| 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 RelTopicId1 asp 2/12/2006 11:40:40AM 0 (This is a new thread)2 RE asp 2/12/2006 11:56:10 PM 1 (A reply on subject asp)3 RE asp 2/12/2006 12:45:12 PM 1 (A reply on subject asp)4 Vb.net 2/13/2006 13:40:54 PM 0 (A new thread)5 RE Vb.net 2/13/2006 13:59:51 PM 4 (A reply on Vb.net)6 RE asp 2/14/2006 10:20:30 AM 1 (A replt on asp)TopicId 1 and RelTopicId 0 is the parent, and all the RelTopicId 1 is the child to the Parent which is TopicId 1."note all RelTopicId = 0 is a new thread"here is the sql statement to retrive dataselect * 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. This is to get the Parent(TopicId = 1, RelTopicId = 0) and the child which is (all RelTopicId = 1)i have try this "select * from Forum where Date >='2006-2-13' and Date <='2006-2-14 and TopicId = "0" and RelTopicId = TopicId"i got no luck. any help appreciate thanks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-24 : 22:35:16
|
>>my question is how to retrive the parent and the child dataIf there is just the one layer of hierarchy then a single self outer join should do it:select p.topicid as parentTopicID ,p.subject ,c.topicid as childTopicIDfrom forum pleft join forum c on c.RelTopicID = p.Topicid Be One with the OptimizerTG |
 |
|
|
|
|
|
SQL help