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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

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 …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

734 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