How to SELECT based on another SELECT with multiple rows as response?

Question

NB: Not being a savvy SQL coder I admit that my googling prior to asking might be flawed. Please have patience if I missed something obvious to others.

I have a select statement, which gives a single row or a multiple rows as a reply. Then, I'm executing a different select statement with a parameter that is fetched from the first row. Then, I execute the very same select statement again but with the parameter fetched from the second row and so on.

I'd like to put that into a single statement in SQL but, regrettably enough, I lack the skils for that. How do I do that?

At the moment, I'm fetching the first set of data separately. Then, I do some C# magic and execute each of the following, parametrized, statements individually. Waaay not neat...

The issue can be restated as follows. Of course, that's not how I'll solve it in the end (there'll be an SP doing that) but it shows the approach we use today, which we're very discomforted about.

String mainCommand = "select Index from SomeTable";
...
foreach(String parameter in ...)
{
  String subCommand = 
    "select RealValue from AnotherTable where Index = " + parameter;
  ...
}

Answer 1

You want to use a join operation and a subquery to perform your operation all at the same time.

Something like this:

 SELECT [fields] from tbl_mainCommand
 JOIN 
 (SELECT [fields] from tbl_subCommand) 
     on tbl_mainCommand.fieldKey = tbl_subCommand.fieldKey

Depending on how your tables are set up, the subquery may not be the most efficient way of doing this as it could result in a correlated subquery. But ultimately the nice thing about SQL and Set Theory is that you can perform a massive operation like this all at once as opposed to iterating over a number of parameters and running multiple SQL statements.

Note that the on tbl... line is your link between the tables. This is essentially where you are passing your parameter into your second table or subquery. If you can look at it more as a link between two different data sets as opposed to passing a parameter, you are more likely to avoid the correlated subquery.

Answer 2

Easiest to do it with CTE like:

With IndexSet AS 
(Select index from SomeTable)
Select RealValue from AnotherTable INNER JOIN 
                      IndexSet ON AnotherTable.INdex=INdexSet.Index