1

Is it possible to select multiple values from a subquery in SELECT block?

Selecting one value works fine like this:

SELECT
  a.id,
  (SELECT b.id FROM b WHERE b.a_id = a.id) AS b_id
FROM
  a

But if i also want to get the b.name and i change the query to this:

SELECT
  a.id,
  (SELECT b.id, b.name FROM b WHERE b.a_id = a.id)
FROM
  a

... it doesn't work anymore. One possibility would be to put the subquery to FROM block and take values from there but in my particular query that doesn't work so i would like to solve in SELECT block. Thank you!

Ahmad
  • 906
  • 11
  • 27
user1985273
  • 1,817
  • 15
  • 50
  • 85
  • the subquery should return only one column and one row. – Jade May 09 '14 at 08:44
  • 1
    if you are sure that your subquery returns only one row then write the sub-query twice, once for id and second for name. – chetan May 09 '14 at 08:45
  • 1
    http://stackoverflow.com/questions/583954/how-can-i-select-multiple-columns-from-a-subquery-in-sql-server-that-should-ha – slavoo May 09 '14 at 08:49

1 Answers1

2

This will help you

SELECT A.ID,
       B.ID,
       B.NAME
FROM   A INNER JOIN B ON B.A_ID=A.ID;
Srini V
  • 11,045
  • 14
  • 66
  • 89