Iterate through records returned by a query

Question

5.00/5 (1 vote)

See more:

I have a query which returns few records. The Query result has a column 'ACCTID'. Now for each record i need to take the ACCTID and fetch record from other table. I tried it to do in C# but using loop, but it is taking lot of time. Pls suggest me how to do the same in SQL server side.

Overview of query is like below:

Main query:

SQL

Select NAME, CANCEL_DATE..., ACCTID from Table1;

What i need to do for ACCTID now is fetch data from two other tables for ACCTID fetched from above main query

:

SQL

select * from table2 where ACCTID=@ACCTID 


select * from table3 where ACCTID=@ACCTID

In the end i need to return all the records fetched from above two queries.

What I have tried:

currently I am trying to use cursor to get it done

Posted 23-Dec-18 22:45pm

Member 10281217

Updated 23-Dec-18 23:08pm

Richard MacCutchan

v2

Add a Solution

Comments

Richard MacCutchan 24-Dec-18 4:56am

Have you considered using a stored procedure?

1 solution

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)

OriginalGriff · Answer 1 · 2018-12-23T23:09:00

Solution 1

Use a JOIN:

SQL

SELECT a.Name, a.Cancel_Date, b.*, c.* FROM Table1 a
JOIN Table2 b ON a.ACCID = b.ACCID
JOIN Table3 c ON a.ACCID = c.ACCID
WHERE a.ACCID = @ACCID

Posted 23-Dec-18 23:09pm

OriginalGriff

Comments

W∴ Balboos, GHB 24-Dec-18 6:56am

In general, and slightly leaving the scope of the question, would a DISTINCT directive be a good idea to add to this? Not knowing what the data looks like, the potential for any number of duplicate records exists (particularly if there's no unique id'ish type field in table2 or table3)?

Of course, they may want the duplicates.

OriginalGriff 24-Dec-18 7:13am

As you say, it depends on exactly what they are trying to do!