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
193 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
Comment Utility

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

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
Comment Utility
Excellect Worked very well,
0
 

Author Comment

by:mj_stanton
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now