sql subquery from where in

Question

I'm writing something along these lines:

select fielda,fieldb,
       (select sum(field1)-sum(field2) as fieldc 
        from tableb 
        where fieldid = list_of_ids[i]) 
from tablea 
where fieldid in (list_of_ids);

In the subquery I'd like to use the value from the outer where clause. So if list_of_ids is 123,456,789 the fieldid in the subquery will be 123, the second 456, etc. Is this possible?

Answer 1

You just need to use ta.fieldid instead of list_of_ids[i]

select ta.fielda,ta.fieldb,
       (select sum(tb.field1)-sum(tb.field2) as fieldc 
        from tableb tb
        where tb.fieldid = ta.fieldid ) 
from tablea  ta
where ta.fieldid in (list_of_ids);