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
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