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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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