Show Access DB-content with i.e. WHEN-statement??

[Bob the ASP Builder]

I have a database with an autoincrement ID-column and a ParentID-column.

Its used in a sort of "thread-environment"

When a new thread is created the Parent-ID gets value 0.
When a new posting is a comment to a previous one the ParentID-value
equals the ID-value for the parent-posting.

Like this

ID ParentID Indatatime
1 0 03.01
2 0 03.29
3 0 06.10
4 1 07.38
5 0 11.15
6 3 11.30
7 1 11.59

Now I want to pull them from the Access-DB and present them very
logically with every ID with parentID = 0 followed by its respective
comments - like this

ID ParentID Indatatime
1 0 03.01
4 1 07.38
7 1 11.59
2 0 03.29
3 0 06.10
6 3 11.30
5 0 11.15


This line almost does it
SQL = "SELECT * FROM MYTABLE ORDER BY ParentID DESC"

but the postings with identical ParentID sometimes present themselves
irrational. That is why I have added an Indatatime column - but I have
failed in creating a solution that does the above.

This for ex - doesnt do it - gives syntax error
SQL = "SELECT * FROM MYTABLE ORDER BY CASE WHERE ParentId 0 THEN ID ELSE
ParentID END, INDATATIME DESC"

Good ideas somebody??


Bob the ASP Builder

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Manohar Kamath [MVP]]

Bob,

One of the solutions is to create the ID that contains all the parent keys,
and also level info.

e.g.

ThreadID ParentID Level Message
-------- --------- ----- -----------------------
1 0. 1 First Message
2 0.1. 2 Responding to "First Message"
3 0.1.2. 3 Responsing to "Responding to First Message"
4 0.1 2 Responding to "First Message"


The parent ID is created by concatenating the parentID of the parent thread
+ the thread ID of the parent thread. This way, you don't have to traverse
all the way up to the top to find out all the ancestors. Plus, you can do
some neat things with a composite parent ID like Delete/Update all the
threads below a given message.

To show the first level threads, you can simply do

SELECT * FROM MYTABLE WHERE ParentID='0.' ORDER BY ThreadID DESC

To show, say second level threads under threadID 1, do

SELECT * FROM MYTABLE WHERE ParentID='0.1.' ORDER BY ThreadID DESC

To show all the threads under threadID 1, do

SELECT * FROM MYTABLE WHERE ParentID LIKE '0.1.*' ORDER BY ThreadID DESC

Hope that gives you some ideas how to implement your discussion group.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Bob the ASP Builder" <anonymous@devdex.com> wrote in message
news:ujKknY41DHA.1184@TK2MSFTNGP10.phx.gbl...

> I have a database with an autoincrement ID-column and a ParentID-column.
>
> Its used in a sort of "thread-environment"
>
> When a new thread is created the Parent-ID gets value 0.
> When a new posting is a comment to a previous one the ParentID-value
> equals the ID-value for the parent-posting.
>
> Like this
>
> ID ParentID Indatatime
> 1 0 03.01
> 2 0 03.29
> 3 0 06.10
> 4 1 07.38
> 5 0 11.15
> 6 3 11.30
> 7 1 11.59
>
> Now I want to pull them from the Access-DB and present them very
> logically with every ID with parentID = 0 followed by its respective
> comments - like this
>
> ID ParentID Indatatime
> 1 0 03.01
> 4 1 07.38
> 7 1 11.59
> 2 0 03.29
> 3 0 06.10
> 6 3 11.30
> 5 0 11.15
>
>
> This line almost does it
> SQL = "SELECT * FROM MYTABLE ORDER BY ParentID DESC"
>
> but the postings with identical ParentID sometimes present themselves
> irrational. That is why I have added an Indatatime column - but I have
> failed in creating a solution that does the above.
>
> This for ex - doesnt do it - gives syntax error
> SQL = "SELECT * FROM MYTABLE ORDER BY CASE WHERE ParentId 0 THEN ID ELSE
> ParentID END, INDATATIME DESC"
>
> Good ideas somebody??
>
>
> Bob the ASP Builder
>
> *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
> Don't just participate in USENET...get rewarded for it!

[Bob Barrows]

Manohar's suggestion is good, but it involves error-prone processing when
putting the data in.
An alternative design is called "nested sets", which would make getting this
resultset possible with a single query. Here is a link:
[url]http://www.intelligententerprise.com/001020/celko.shtml[/url]


HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob the ASP Builder]

Well - Manohar !

Your suggestions sounds exactly what I want - and I have really tried
hard to implement this - and succeeded - but only to some degree.

I cant get this bit of code to work - whatever I try

SQL = "SELECT * FROM MYTABLE WHERE ParentID LIKE '0.*'"

Eventhough there actually are inputs with the ParentID value 0.1.

It DOES work if I set the value to ParentID = 0.1 (just for the check)
but that of course is not practically useful

Whats wrong with my code? Im lost

Bob the ASP Builder

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!