Chaining SQL queries

Question

Right now I'm running

SELECT formula('FOO') FROM table1
WHERE table1.foo = 'FOO' && table1.bar = 'BAR';

but I would like to run this not on the constant FOO but on each value from the query

SELECT foos FROM table2
WHERE table2.bar = 'BAR';

How can I do this?

Edit: Important change: added FOO to the arguments of function.

Illustration:

SELECT foo FROM table1 WHERE foo = 'FOO' && table1.bar = 'BAR';

gives a column with FOOa, FOOb, FOOc.

SELECT formula('FOO') FROM table1
WHERE table1.foo = 'FOO' && table1.bar = 'BAR';

gives a single entry, say sum(FOO) (actually much more complicated, but it uses aggregates at some point to combine the results).

I want some query which gives a column with sum(FOO1), sum(FOO2), ... where each FOOn is computed in like manner to FOO. But I'd like to do this with one query rather than n queries (because n may be large and in any case the particular values of FOOn vary from case to case).

Answer 1

Try this one:

SELECT formula FROM table1
WHERE table1.foo IN(SELECT foos FROM table2
WHERE table2.bar = 'BAR';
) AND table1.bar = 'BAR';

Answer 2

SELECT formula FROM table1 WHERE bar = 'BAR' AND foo IN (SELECT foos FROM table2 WHERE bar = 'BAR');

EDIT:

This isn't tested, but perhaps this will work?

SELECT formula(q1.foo) FROM table1 INNER JOIN (SELECT foo, bar FROM table2) q1 ON table1.foo = q1.foo WHERE table1.bar = 'BAR';