CODEWITHSUNDEEP
sqlhive
Krishna
Krishna

Reputation: 153

How to return multiple values from a subquery

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!

Upvotes: 2

Views: 5574

Answers (4)

TommCatt
TommCatt

Reputation: 5636

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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().

Upvotes: 1

wes.hysell
wes.hysell

Reputation: 1209

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)

Upvotes: 0

Matt
Matt

Reputation: 15061

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.

Upvotes: 0

Related Questions