How to convert this query in derived table concept

0
votes

Updated :

How to introduce this below query in derived tables

( 3959 * acos( cos( radians(36.247137) ) * cos( radians( cm.lat ) ) * cos( radians(cm.lng ) - radians(-115.221032) ) + sin( radians(36.247137) ) * sin( radians( cm.lat ) ) ) ) AS distance

in this MySql query :

MySql Query :

SELECT ( 3959 * acos( cos( radians(36.247137) ) * cos( radians( `cm`.`lat` ) ) * cos( radians(`cm`.`lng` ) - radians(-115.221032) ) + sin( radians(36.247137) ) * sin( radians( `cm`.`lat` ) ) ) ) AS distance, min(pl.price_pure) as minPrice, max(pl.price_pure) as maxPrice, min(pl.`squarefeet_pure`) as minSquare, max(pl.`squarefeet_pure`) as maxsquare FROM  `plans` pl INNER JOIN property pr ON pr.id = pl.property_id INNER JOIN communities cm ON cm.id = pr.community_id Having distance < 20 order by pl.price_pure, pl.squarefeet_pure

..........................................................................

Prev query heading was : Why no result when applying Max, MIN price formula in MySql query

Detailed question:

http://pastebin.com/UR48mbtq here is my mysql query .. i want to get the max and min price of homes which are falling under 20 miles distance.. but the result is null. Distance in example is 20 but i have also result in my database with 20 miles and less 20 miles

MySql Query :

SELECT ( 3959 * acos( cos( radians(36.247137) ) * cos( radians( `cm`.`lat` ) ) * cos( radians(`cm`.`lng` ) - radians(-115.221032) ) + sin( radians(36.247137) ) * sin( radians( `cm`.`lat` ) ) ) ) AS distance, min(pl.price_pure) as minPrice, max(pl.price_pure) as maxPrice, min(pl.`squarefeet_pure`) as minSquare, max(pl.`squarefeet_pure`) as maxsquare FROM  `plans` pl INNER JOIN property pr ON pr.id = pl.property_id INNER JOIN communities cm ON cm.id = pr.community_id Having distance < 20 order by pl.price_pure, pl.squarefeet_pure

With this Result:

distance              minPrice   maxPrice   minSquare   maxsquare
1754.3401139613718       0       21000003     0            8607

But when i am adding Having clause then result is null with below query

SELECT ( 3959 * acos( cos( radians(36.247137) ) * cos( radians( `cm`.`lat` ) ) * cos( radians(`cm`.`lng` ) - radians(-115.221032) ) + sin( radians(36.247137) ) * sin( radians( `cm`.`lat` ) ) ) ) AS distance, (pl.price_pure) as minPrice, (pl.price_pure) as maxPrice, (pl.`squarefeet_pure`) as minSquare, (pl.`squarefeet_pure`) as maxsquare FROM  `plans` pl INNER JOIN property pr ON pr.id = pl.property_id INNER JOIN communities cm ON cm.id = pr.community_id Having distance < 20 order by pl.price_pure, pl.squarefeet_pure

Update : if i am removing max, min formula from query then also its giving me 400 result ..

2
mysqldistancelatitude-longitude
instead of having can you try with where ? - krishn Patel
@krishnpatel its virtual field and i thing where cant apply here. also i tried before. - Puzzled Boy
CAST(Distansc login AS UNSIGNED) AS distance. can you try it - krishn Patel
distance < 20 mean 1754.34 < 20 is false. you should write > to get this data - krishn Patel
@krishnpatel i want to find the result upto 20 miles.. that should not be greater than 20 miles.. - Puzzled Boy

2 Answers

1
votes

Having is a clause that is applied after the query rows are generated. Since you have aggregate functions in your query, but no GROUP BY, mysql provides you a single (sample) distance result and then the min and max for the entire dataset.

After that, the having clause is applied - and as @krishnpatel pointed out, 1754 < 20 is false, so that row is dropped and you're left with nothing.

Add a GROUP BY in your selection, equivalent to your distance calculation, to return the result you're looking for.

0
votes

First gather ids for the properties within 20 miles.

Then use that as a subquery and JOIN to the main table to do the rest:

SELECT MIN(...), ...
    FROM ( SELECT id FROM tbl WHERE ... < 20 ) AS x
    JOIN tbl ON x.id = tbl.id;

No HAVING, no GROUP BY (unless I missed something).