Unanswered: using multiple select statement in stored procedure
I need to get the output from a select statement, which could be one or more records, and use them as criteria for another select statement, and then use the output from the second select as criteria for a third select, and so on and so forth, until the select returns nothing. What I need to get from the stored procedure is all the output combined together.
I presume that @id is the criteria that is going to change, I would create a temp table to load the results into and then do a while loop which checks an if exists on the select statement you are going to run. Then in the while loop insert the results from the select statement into the temp table. Finally carry out the alteration on @id. Close the while loop.
Once out of the while loop select all from the temp table (maybee using distinct to avoid row replication).
I couldn't decipher much of the details from your message of exactly what you are trying to do, but you can join a table on itself. You can also join on a subselect. The last one is a great way to get to a record based on an aggregate function.
Take this example... Say you have a table that logs everytime someone calls a proc. And you want to see the most recent record for that call. Many people would write the SQL this way:
WHERE ProcName = 'spTryMe'
AND LastCalled = MAX(LastCalled)
I used to get students on this one back when I taught SQL in college. you can't do the syntax above. You can't include an aggregate function like that in the where clause. The easiest way for this is to use a sub-select as such:
WHERE ProcName = 'spTryme'
AND LastCalled = (SELECT MAX(LastCalled) FROM ProcAuditTbl WHERE ProcName = 'spTryMe')
So, back to your issue... Depending on exactly what you want to gather, you could use sub-selects or join the table to itself. Last resort would be to use a temp table. In 99% of circumstances, sub-selects and joins will out perform a temp table.