Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 836
Rahmani Avatar asked Jan 22 '14 04:01

Rahmani


People also ask

How can I use one query result in another?

Use the results of a query as a field in another query. You can use a subquery as a field alias. Use a subquery as a field alias when you want to use the subquery results as a field in your main query. Note: A subquery that you use as a field alias cannot return more than one field.


1 Answers

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
like image 115
Krisztián Balla Avatar answered Oct 02 '22 09:10

Krisztián Balla