loops in sql query please help

Question

1.00/5 (1 vote)

See more:

i want to get N number of records in sql server based on a condition

I'm trying to maintain a track of records based on a condition ..

I'm having a table which is storing some messages ..

and on more table which stores FromId and ToId when a user type message and Click on

Send Button then the message will be stored in message table, FromId Retrieves it as Sent

Message where as ToId Retrieves it as Inbox Message...

now my Requirement is to add a Reply column to my ID table which stores previous MessageId.

now i need to Retrieve the entire conversation based on the chain using a loop.

like while (if exists (select Reply from tbl_id )

{
do some thing;
}

if it encounters null the loop should break

Posted 15-Dec-13 23:16pm

Member 10468587

Updated 16-Dec-13 0:47am

v2

Add a Solution

Comments

thatraja 16-Dec-13 5:20am

Not clear little bit, Rephrase your question, be specific

Member 10468587 16-Dec-13 6:47am

please read my qustn again and try to help me

Maciej Los 16-Dec-13 5:36am

Could you be more specific and provide more details about your needs? Can you add sample data for better understanding?

Member 10468587 16-Dec-13 6:48am

due to some restrictions i'm not suppose to do so.. plz re check my questoion

thatraja 16-Dec-13 6:52am

Then use dummy Table/column names with more details. Share the sample table structure & sample data. And the output which you want. By that way we could think. Now I could give you solution but don't want to give a wrong one. So include your details clearly in your question

3 solutions

Solution 1

While there aren't loops in a SQL quesry (you may use them, for instance, in PL/SQL) you may obviously retrieve a set of records satisfying a given condition, that is the very purpose of the SQL queries.

Posted 15-Dec-13 23:33pm

CPallini

Solution 2

Maybe it gives you an idea. Maybe I'm completely wrong with my assumption. N rows implies me something like (depending on what SQL Server):

SELECT ..... LIMIT N

SELECT TOP N ....

Posted 16-Dec-13 1:06am

idle63

Comments

Member 10468587 16-Dec-13 7:54am

N is not fixed

[no name] 16-Dec-13 7:59am

Please be more specific. Of course I do not assume that N is fixed. But maybe my assupmtion is wrong?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2013-12-16T05:08:00

Solution 3

Let say you want to retrieve a backward chaining of all messages preceding a particular message, assuming the reply_id is nullable, my idea is as follows:

declare @id varchar(255)
set @id = "some message id"
While (@id is not null)
begin

   select @id = reply_id from tbl_id where message_id = @id
   if @id is not null /* do something. */
end

Posted 16-Dec-13 5:08am

Peter Leow

Comments

Member 10468587 19-Dec-13 3:07am

appreciable.. superb... tnx a lot

Peter Leow 19-Dec-13 5:04am

You are welcome.