Geek Answers Handbook

MySQL group Does not work in subquery

I have this request and it works great. I use MIN(home_price)to display as the starting price, and I use this query for the api and WHERE clauses that are added to it, so if I search by price, it will change MIN(home_price).

SELECT MIN(home_price) as min_home_price,
   id,
   name,
   community,
   maplocation,
   locationLabel,
   logo 
FROM ourCommunity 
INNER JOIN readyBuilt 
   ON community = home_community 
INNER JOIN rb_locations 
   ON readyBuilt.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt 
                        ON community = home_community 
                 WHERE isDeleted = 0 AND is_upcoming = 0) 
   AND home_status = 1 
 GROUP BY id,name,community,mapLocation,locationLabel,logo 
 ORDER BY name

So, I decided to use a subquery

SELECT id,
   name,
   community,
   maplocation,
   locationLabel,
   logo, 
   (SELECT MIN(home_price) as min_home_price 
          FROM ourCommunity 
          INNER JOIN readyBuilt 
                 ON community = home_community 
          INNER JOIN rb_locations 
                 ON readyBuilt.home_location = rb_locations.locationId 
          WHERE id IN ( SELECT DISTINCT id 
                        FROM ourCommunity 
                        INNER JOIN readyBuilt 
                               ON community = home_community 
                        WHERE isDeleted = 0 
                               AND is_upcoming = 0) 
                 AND home_status = 1 
          GROUP BY id,name,community,mapLocation,locationLabel,logo 
          ORDER BY name) as org_min_home_price 
FROM ourCommunity 
INNER JOIN readyBuilt 
   ON community = home_community 
INNER JOIN rb_locations 
   ON readyBuilt.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt 
                        ON community = home_community 
                 WHERE isDeleted = 0 AND is_upcoming = 0) 
   AND home_status = 1 
 GROUP BY id,name,community,mapLocation,locationLabel,logo 
 ORDER BY name

But when I execute the second request, I get this error

Subquery returns more than 1 row

When I delete GROUP BY, I do not get errors using MIN(home_price), for each line the same. Does anyone have any suggestions on how to accomplish what I'm trying to accomplish?

+4
source share
3 answers

, price , .

min (home_price),

SELECT min_home_price,   
   id,
   name,
   community,
   maplocation,
   locationLabel,
   logo 
FROM ourCommunity
INNER JOIN (select min(home_price) min_home_price, home_community from readyBuilt group by home_community) b on b.home_community = community
INNER JOIN readyBuilt a ON community = a.home_community 
INNER JOIN rb_locations ON a.home_location = rb_locations.locationId 
WHERE id IN ( SELECT DISTINCT id 
                 FROM ourCommunity 
                 INNER JOIN readyBuilt ON community = home_community 
                 WHERE isDeleted = 0) 
AND home_status = 1 
GROUP BY id,name,community,mapLocation,locationLabel,logo 
ORDER BY name;
+5

MySQL , MySQL , .

:

SELECT b.min_home_price,   
oc.id, oc.name, oc.community, oc.maplocation, oc.locationLabel, oc.logo
FROM ourCommunity oc
INNER JOIN (select min(home_price) min_home_price, home_community from readyBuilt group by home_community) b on b.home_community = oc.community
INNER JOIN readyBuilt rb ON oc.community = rb.home_community 
INNER JOIN rb_locations rbl ON rb.home_location = rbl.locationId 
INNER JOIN readyBuilt rb1 ON oc.community = rb1.home_community
WHERE rb1.isDeleted = 0
AND oc.home_status = 1 
 GROUP BY oc.id, oc.name, oc.community, oc.mapLocation, oc.locationLabel, oc.logo 
 ORDER BY oc.name;

3 , WHAT , HOW, , , .

0

, , min (home_price) .

"group by", min (home_price) , ..

, min (home_price) . ( ).

, - . , , , , . ( ).

Select identifier, name, etc. , (select min (price) from p where p.id = oc.id) as minPrice from our community internal connection, etc. group by...

As the other answers showed, there are many ways to achieve this, for example, combining instead of subqueries. For each, there are pros and cons, unless performance is a problem, just use what is easiest to understand and maintain.

0
source

More articles:


All Articles