Solved

How do I retrieve database results from one field across multiple rows as strings so that I can use them in a second SQL Query?

Posted on 2006-07-19
4
197 Views
Last Modified: 2010-04-23
Hi

I have a need for some code to retrieve results from one field across multiple rows as strings so that I can use them in a second SQL Query. I am using VB.net2003 to create a Winforms application with a SQL 2000 Database Server in the backend. For example the sample code I am looking for will allow me to do something like the following;

SQLCommand1 = SELECT ProjectID FROM Table_Projects WHERE StaffID= @StaffID

RESULTS (Expected Between 0 and ? rows)
Table_Projects.ProjectID = @ProjectID_Row1
Table_Projects.ProjectID = @ProjectID_Row2
Table_Projects.ProjectID = @ProjectID_Row3

Table_Projects.RowCount = 3

Magic happens somewhere here or above????

SQLCommand2 = SELECT * FROM TableDocments WHERE (DocumentID = @ProjectID_Row1) AND (DocumentID = @ProjectID_Row2) AND (DocumentID = @ProjectID_Row3)

I assume doing this in code is the way to go, if it is better to do all this on the Server in some sort of multi-step SQL Stored Procedure please let me know.

Thanks in Advance

Matthew
 



0
Comment
Question by:mj_stanton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 6

Accepted Solution

by:
Nandakumar Sakthivel earned 500 total points
ID: 17143839

 If i haven't misunderstood your question
 why don't you use like this directly in the query

           SELECT * FROM TableDocments WHERE DocumentID in (select  SELECT ProjectID FROM Table_Projects WHERE StaffID= @StaffID)

Thanks,
Nanda

0
 

Author Comment

by:mj_stanton
ID: 17143883

Wow, I may be showing my SQL inexperience here I have not seen a Query within a Query before but I will give it a go by testing it on the SQL server.

I made one typo in my question  "SQLCommand2" which would change you suggested solution to be as follows.

SELECT * FROM TableDocments WHERE TableDocments.ProjectID in (select  SELECT ProjectID FROM Table_Projects WHERE StaffID= @StaffID)

I will get back to you soon

Matthew
0
 

Author Comment

by:mj_stanton
ID: 17143937
Excellect Worked very well,
0
 

Author Comment

by:mj_stanton
ID: 17143948
Sorry for the benifit of others I should say that I had to remove the Second "Select" Statement so the solution looked like this.

SELECT * FROM TableDocments WHERE TableDocments.ProjectID in (SELECT ProjectID FROM Table_Projects WHERE StaffID= @StaffID)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question