0

i have this scenario with this query that trasform multiple rows key/values in a columns.

SELECT
  t1.*, t2.*, u.name, u.email, u.username, 
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1 
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
LEFT JOIN `f9leh_users` AS u ON t1.uid = u.id
GROUP BY t1.uid

and i need to add a where clause to call the latitude value but adding clause for example

where latitude > 0 

and after i need to call the colum to replace the new a.lat with latitude column name

 $distance_col_expression = "(((acos(sin((".$post['socialmembers']['location_lat']."*pi()/180)) *
                            sin((a.lat * pi()/180))+cos((".$post['socialmembers']['location_lat']." * pi()/180)) *
                            cos((a.lat * pi()/180)) * cos(((".$post['socialmembers']['location_lng']."- a.lng)
                            *pi()/180))))*180/pi())*60*1.1515) 
                            ";
                        $query .= ' AND '.$distance_col_expression .' <= '.$post['socialmembers']['search_radius'];

it not work. Says invalid column name. I have tried with other names but seems that this name generated is temporary.

Please can suggest me, How can i do? Thanks in advance

closed as off-topic by Paul White, RolandoMySQLDBA, RLF, Max Vernon, Philᵀᴹ Jan 15 '15 at 17:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, RLF, Max Vernon, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    You need to either use a subquery or place that latitude code in a having clause. – Taryn Jan 9 '15 at 19:28
  • hI @blueefeet , please can you show me some example. I am not a query expert and i have no idea of how to do this. Thanks – Mark Jan 9 '15 at 19:37
  • Look in dev.mysql.com/doc/refman/5.1/en/subqueries.html for subqueries. It's not too difficult, it's essentially putting a query inside parenthesis and using the result as criteria for the nesting query. – P. O. Jan 9 '15 at 20:25
  • Why is there a self-join to #_social_fields_data table? – ypercubeᵀᴹ Jan 12 '15 at 12:35
1

Thanks for the useful suggestion.

Now the query work as in this way:

select DISTINCT t3.uid,latitude,longitude
from (
  SELECT DISTINCT 
  t2.*, 
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1 
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
GROUP BY t1.uid
     ) as t3
WHERE latitude > 0 

Thanks, as suggested i have optimized the query removing self_join:

select DISTINCT t2.uid,latitude,longitude
from (
  SELECT DISTINCT 
  t1.*, 
  MAX(CASE WHEN t1.datakey = 'latitude' THEN t1.data END) AS latitude,
  MAX(CASE WHEN t1.datakey = 'longitude' THEN t1.data END)AS longitude
FROM `#_social_fields_data` AS t1 
GROUP BY t1.uid
     ) as t2
WHERE latitude > 0 

Not the answer you're looking for? Browse other questions tagged or ask your own question.