Tuesday, 13 August 2013

How to select an "AS field" in MySql?

How to select an "AS field" in MySql?

I have a Query which is separated in different parts. (distance, score and
rank)
SELECT Entry.*, Address.*,
(6367.41 * SQRT(2 * (1-cos(RADIANS(Entry.latitude)) *
cos(0.92640848333131) * (sin(RADIANS(Entry.longitude)) *
sin(0.15361853481704) + cos(RADIANS(Entry.longitude)) *
cos(0.15361853481704)) - sin(RADIANS(Entry.latitude)) *
sin(0.92640848333131))))
AS distance,
(CASE WHEN `Entry`.`title` LIKE '%%' THEN 50 ELSE 0 END +
CASE WHEN `Entry`.`description` LIKE '%%' THEN 30 ELSE 0 END +
CASE WHEN `Entry`.`description_long` LIKE '%%' THEN 10 ELSE 0 END +
CASE WHEN `Entry`.`product_type` = 1 THEN 0 ELSE 0 END +
CASE WHEN `Entry`.`product_type` = 2 THEN 40 ELSE 0 END +
CASE WHEN `Entry`.`product_type` = 3 THEN 50 ELSE 0 END )
AS score,
(CASE WHEN (score > 100 AND distance <= 10) THEN 1 ELSE 0 END)
rank
FROM `usr_web12_1`.`entries` AS `Entry`
inner JOIN `usr_web12_1`.`entrieslocations` AS `Entrieslocation` ON
(`Entry`.`id` = `Entrieslocation`.`entry_id`)
inner JOIN `usr_web12_1`.`addresses` AS `Address` ON (`Address`.`id` =
`Entry`.`address_id`)
WHERE ((`Entry`.`title` LIKE '%%') OR (`Entry`.`description` LIKE '%%') OR
(`Entry`.`description_long` LIKE '%%') OR (`Entry`.`meta_keywords` LIKE
'%%') OR (`Entry`.`filter_keywords` LIKE '%%')) AND `Entry`.`status` = 1
AND
`Entry`.`latitude` Between 52.179974594081 AND 53.978617805919 AND
`Entry`.`longitude` Between 7.3045938084915 AND 10.298793591508 AND
`Entrieslocation`.`category_id` = 1
GROUP BY `Entry`.`id`
ORDER BY `Entry`.`product_type` desc
LIMIT 10
Question: The rank-part doesn't work "Column not found: 1054 Unknown
column 'score' in 'field list'", how can I access a dynamic AS-Field???
Same problem with distance...
Thanks a lot!

No comments:

Post a Comment