any answers for this one?

[smartipants]

I have created a Discussion forum where in people can post in their messages or problems and any person having an answer to it can post the same.One message may or may not have any answers to it and a message may be having many answers posted to it.
Now my question is what sql query should I write to delete a message as I don't know whether that message contains any sub-messages or answers to it which should automatically get deleted as I delete its parent message?

PLEASE SOMEBODY HELP!!!!!!!

IT'S URGENT

 

[bperry]

You may have to post a few more details about the tables involved and their relationships. Are sub-messages and answers stored in separate tables, or within the same table? In either case, how are the submessages and answers linked to the original message?

 

[smartipants2002]

well,the tables involved are:
1)topic
2)post
wherein the topic table holds all the topics of the forum and the post table holds all the messages of the forum.

The sub-messages and answers stored within the same table.

Each messages (ie top level messages and sub messages)are given a unique number say a message's primary key number is 8 and two submessages are posted to it,their automatically generated unique primary key number is say 9 and 10 each of these replies will hold the primary key number of their parent in the field called response.

 

[bperry]

The most straightforward statement would be something like this:
delete from posts where pkid = 8 or response = 8

I think this would work, but might have problems if Response is a Foreign Key linked to the PkId. Try it?

Other than that, then the delete will have to be in two statements:
Delete from Posts Where Response=8
Delete from Posts Where PkId = 8


In either case, you should put these commands into stored procedures if your database allows it, and pass in the Id to be deleted as a variable.

 

[bperry]

P.S. You'll get more out of the tek-tips site if you join as a member, rather than contribute as a Visitor. For one thing, you'll get automatic e-mail notification if someone replies to your post.

bp

 

[smartipants2002]

Thanks.But let me point to you the problem that is what if there are submessages to this sub message ie going by our same above example:
Each messages (ie top level messages and sub messages)are given a unique number say a message's primary key number is 8 and two submessages are posted to it,their automatically generated unique primary key number is say 9 and 10 each of these replies will hold the primary key number of their parent in the field called response.

now there submessages identified by 9 and 10 can have several responses to it or you can say sub-submessages.so now these sub-submessages will hold the primary key number of their parent ie 9 or 10 in the field called response.

Now how will i come to know as to which messages have child messages to it because the child messages too needs to deleted once its parrent has been deleted because it will still be pointing to an unique value message which has been deleted.

How will i come to know which messages have child messages and how much.there are n number of mesages posted in the forum.

PLEASE HELP

 

[bperry]

oh, I see.
That is a problem.
I'm temporarily stumped, but I'll try to think of something. Hopefully another forum reader with experience in tree structures might have a suggestion.

 

[bperry]

By the way, what database are you using?

 

[bperry]

Hi again,
I have thought about this some more, and have these comments.

(1) In general, this would be a trivial request if we had one more column in the Posts table, containing the primary or 'father' id that all sub-answers, and their sub-answers, relate to. i.e in your example above, records 8, 9, 10, 11, and 12 would all have a value of '8' in this new column. If we had that, then this deletion request would be dead easy. You should consider modifying the application to do that, if that is possible.

(2) Leaving aside that idea, I think a single SQL statement to do this deletion is impossible.

(3) A solution is possible by building a temporary work table, running a loop to insert records from Posts into the work table, and then running a Delete statement that is able to link from Posts to the work table. It wouldn't be too pretty, but I think it would work.

But this would require writing some extra code, and probably wrapping that code up inside a query or a stored procedure (which it not a bad idea anyway). I could perhaps help write that code only if you happen to be using microsoft SQL Server.
-----------------
If you are using some other database, you might get more responses if you post your problem to the forum for that particular product. ie. right here we are in the ANSI SQL forum, which tends to get general questions and at any rate is relatively quiet compared to the other forums for specific products.

Hope you find something in there that helps a bit. (Hope you can add that new column to Posts!)

vperry

 

[smartipants]

Thanks.You are such a help.I would be grateful if you would write the code to me,which you mentioned above.
Thanks Again.

smartipants

 

[bperry]

Hi there,
At this point, instead of asking for a few driving tips, I think you are asking me to build you a car. This is a good spot for you to roll up the sleeves and dive in.

bperry

 

[smartipants2002]

Hi,

Thanks for your suggestion.