How to use the result of a select statement as a column of another select statement?

Question

How can I use the result of an SQL select statement as a column of another SQL select statement?

For example:

select a, b,
    (select t from other_table where other_table.id = my_table.a) as c
from my_table

If the result of other_table is one record, there is no problem. But if it has more than one record, it is going to fail.

Answer 1

I usually only use this kind of querying when counting. For example:

SELECT s.name,
       (SELECT COUNT(*) FROM product as p WHERE p.store_id=s.id)
FROM store as s

In this case there is always only one record returned and the query will work. Please note that this is just an example and you should use a join instead of a sub-query here.

In your case you could use the TOP keyword to limit the number of records in your sub-query to one:

select a, b,
    (select TOP 1 t from other_table where other_table.id = my_table.a) as c
   from my_table