SQL query: Iterate over values in table and use them in subquery

Question

I have a simple SQL table containing some values, for example:

id | value    (table 'values')
----------
0  | 4
1  | 7
2  | 9

I want to iterate over these values, and use them in a query like so:

SELECT value[0], x1
FROM (some subquery where value[0] is used)

UNION

SELECT value[1], x2
FROM (some subquery where value[1] is used) 

...
etc

In order to get a result set like this:

4 | x1
7 | x2
9 | x3

It has to be in SQL as it will actually represent a database view. Of course the real query is a lot more complicated, but I tried to simplify the question while keeping the essence as much as possible.

I think I have to select from values and join the subquery, but as the value should be used in the subquery I'm lost on how to accomplish this.

Edit: I oversimplified my question; in reality I want to have 2 rows from the subquery and not only one.

Edit 2: As suggested I'm posting the real query. I simplified it a bit to make it clearer, but it's a working query and the problem is there. Note that I have hardcoded the value '2' in this query two times. I want to replace that with values from a different table, in the example table above I would want a result set of the combined results of this query with 4, 7 and 9 as values instead of the currently hardcoded 2.

SELECT x.fantasycoach_id, SUM(round_points)
FROM (
    SELECT DISTINCT fc.id AS fantasycoach_id,
        ffv.formation_id AS formation_id,
        fpc.round_sequence AS round_sequence,
        round_points,
        fpc.fantasyplayer_id
    FROM fantasyworld_FantasyCoach AS fc

    LEFT JOIN fantasyworld_fantasyformation AS ff ON ff.id = (
            SELECT MAX(fantasyworld_fantasyformationvalidity.formation_id)
            FROM fantasyworld_fantasyformationvalidity
            LEFT JOIN realworld_round AS _rr ON _rr.id = round_id
            LEFT JOIN fantasyworld_fantasyformation AS _ff ON _ff.id = formation_id
            WHERE is_valid = TRUE
                AND _ff.coach_id = fc.id
                AND _rr.sequence <= 2 /* HARDCODED USE OF VALUE */
            )
    LEFT JOIN fantasyworld_FantasyFormationPlayer AS ffp 
    ON ffp.formation_id = ff.id

    LEFT JOIN dbcache_fantasyplayercache AS fpc 
    ON ffp.player_id = fpc.fantasyplayer_id
    AND fpc.round_sequence = 2 /* HARDCODED USE OF VALUE */

    LEFT JOIN fantasyworld_fantasyformationvalidity AS ffv 
    ON ffv.formation_id = ff.id
) x
GROUP BY fantasycoach_id

Edit 3: I'm using PostgreSQL.

Answer 1

SQL works with tables as a whole, which basically involves set operations. There is no explicit iteration, and generally no need for any. In particular, the most straightforward implementation of what you described would be this:

SELECT value, (some subquery where value is used) AS x
FROM values

Do note, however, that a correlated subquery such as that is very hard on query performance. Depending on the details of what you're trying to do, it may well be possible to structure it around a simple join, an uncorrelated subquery, or a similar, better-performing alternative.

---

Update:

In view of the update to the question indicating that the subquery is expected to yield multiple rows for each value in table values, contrary to the example results, it seems a better approach would be to just rewrite the subquery as the main query. If it does not already do so (and maybe even if it does) then it would join table values as another base table.

---

Update 2:

Given the real query now presented, this is how the values from table values could be incorporated into it:

SELECT x.fantasycoach_id, SUM(round_points) FROM
(
    SELECT DISTINCT
        fc.id AS fantasycoach_id, 
        ffv.formation_id AS formation_id, 
        fpc.round_sequence AS round_sequence,
        round_points,
        fpc.fantasyplayer_id

    FROM fantasyworld_FantasyCoach AS fc
    -- one row for each combination of coach and value:
    CROSS JOIN values

    LEFT JOIN fantasyworld_fantasyformation AS ff
    ON ff.id = (
        SELECT MAX(fantasyworld_fantasyformationvalidity.formation_id)
        FROM fantasyworld_fantasyformationvalidity

        LEFT JOIN realworld_round AS _rr
        ON _rr.id = round_id

        LEFT JOIN fantasyworld_fantasyformation AS _ff
        ON _ff.id = formation_id

        WHERE is_valid = TRUE
        AND _ff.coach_id = fc.id

        -- use the value obtained from values:
        AND _rr.sequence <= values.value
    )

    LEFT JOIN fantasyworld_FantasyFormationPlayer AS ffp
    ON ffp.formation_id = ff.id

    LEFT JOIN dbcache_fantasyplayercache AS fpc
    ON ffp.player_id = fpc.fantasyplayer_id

    -- use the value obtained from values again:
    AND fpc.round_sequence = values.value

    LEFT JOIN fantasyworld_fantasyformationvalidity AS ffv
    ON ffv.formation_id = ff.id
) x
GROUP BY fantasycoach_id

Note in particular the CROSS JOIN which forms the cross product of two tables; this is the same thing as an INNER JOIN without any join predicate, and it can be written that way if desired.

The overall query could be at least a bit simplified, but I do not do so because it is a working example rather than an actual production query, so it is unclear what other changes would translate to the actual application.

Answer 2

In the example I create two tables. See how outer table have an alias you use in the inner select?

SQL Fiddle Demo

SELECT T.[value], (SELECT [property] FROM Table2 P WHERE P.[value] = T.[value])
FROM Table1 T 

This is a better way for performance

SELECT T.[value], P.[property]
FROM Table1 T 
INNER JOIN Table2 p
   on P.[value] = T.[value];

Table 2 can be a QUERY instead of a real table

Third Option

Using a cte to calculate your values and then join back to the main table. This way you have the subquery logic separated from your final query.

WITH cte AS (
    SELECT 
        T.[value],
        T.[value] * T.[value] as property
    FROM Table1 T 
)
SELECT T.[value], C.[property]
FROM Table1 T 
INNER JOIN cte C
   on T.[value] = C.[value];

Answer 3

It might be helpful to extract the computation to a function that is called in the SELECT clause and is executed for each row of the result set

Here's the documentation for CREATE FUNCTION for SQL Server. It's probably similar to whatever database system you're using, and if not you can easily Google for it.

Here's an example of creating a function and using it in a query:

CREATE FUNCTION DoComputation(@parameter1 int)
RETURNS int
AS
BEGIN
   -- Do some calculations here and return the function result.
   -- This example returns the value of @parameter1 squared.
   -- You can add additional parameters to the function definition if needed

   DECLARE @Result int
   SET @Result = @parameter1 * @parameter1

   RETURN @Result
END

Here is an example of using the example function above in a query.

SELECT v.value, DoComputation(v.value) as ComputedValue
FROM [Values] v
ORDER BY value