Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
204 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 2000 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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

670 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