How to return multiple values from a subquery

Question

SELECT state, business, a.report
FROM base
WHERE state IN
  (SELECT a.state FROM heart a join (SELECT CAST(MAX(percent_adults) AS DOUBLE) max1 FROM heart)b on (a.percent_adults=b.max1));

In the above subquery, only one value can be returned i.e a.state from table 'heart'. that value is used in the main query and fetches business from 'base' table . I need to return a.report from 'heart' table in subquery in the report along with state and business . Thanks much!

score 1 · Answer 1 · answered Nov 25 '14 at 14:32

Use exists instead of in:

SELECT state, business, a.report
FROM base b
WHERE EXISTS (SELECT 1
              FROM heart h JOIN
                   (SELECT MAX(percent_adults) as max1
                    FROM heart h2
                   ) sb
                   ON h.percent_adults = h2.max1
              WHERE h.state = b.state and h.business = b.business
             );

Don't convert the maximum value, particularly to a floating point representation. It is generally dangerous to compare floating point values for equality, but it should be safe with the result of a MIN() or MAX().

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

See Here Is it possible for a subquery to return two values?

You need to have the subquery in the FROM clause to do this.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 25 '14 at 14:29

Matt

14,906
27
99
149

score 0 · Answer 3 · answered Nov 25 '14 at 14:31

Outside of defining the subquery twice, I am not sure there is a more efficient way to accomplish this with subqueries.

However, perhaps a JOIN would be more useful here:

SELECT a.state, business, a.report
FROM base b
JOIN heart a ON (a.percent_adults = b.max1 AND b.state = a.state)

score 0 · Accepted Answer · answered Dec 02 '14 at 19:27

You don't need the subquery to return two values. All you need the subquery for is to provided the unique join value (relying on the odds that two calculated double values will ever be exactly the same).

So just do a join and choose the report from that:

select  b.state, b.business, a.report
from    base  b
join    heart a
  on    a.state = b.state
where   a.percent_adults =(
          select  max( percent_adults )
          from    heart );

You're limiting the join to just the one state with the highest percent_adult value and getting the report from that.

How to return multiple values from a subquery

4 Answers4

Linked