0

I have a mysql query that works perfectly without the HAVING clause, but when I use that clause I get an error and I think it has to do with the fact that I'm using the clause for a comparison with a column name that I declare in my select.

The Query:

SELECT
  DISTINCT p.ID,
  p.post_title,
  practice_latitude.meta_value as locLat,
  practice_longitude.meta_value as locLong,
  (
    '3959' * acos(
      cos(radians('39.40517956963392')) * cos(radians(practice_latitude.meta_value)) * cos(
        radians(practice_longitude.meta_value) - radians('-118.99713149999997')
      ) + sin(radians('39.40517956963392')) * sin(radians(practice_latitude.meta_value))
    )
  ) AS distance
FROM
  wp_posts p
  INNER JOIN wp_postmeta practice_latitude ON p.ID = practice_latitude.post_id
  INNER JOIN wp_postmeta practice_longitude ON p.ID = practice_longitude.post_id
WHERE
  p.post_type = 'awpcp_listing'
  AND p.post_status = 'publish'
  AND practice_latitude.meta_key = '_awpcp_extra_field[36]'
  AND practice_longitude.meta_key = '_awpcp_extra_field[37]'
HAVING
  distance < 500
group by
  practice_latitude.meta_value,
  practice_longitude.meta_value;

It seems like the 'distance' column being declared in the select is the issue though. How can I correct that to apply the appropriate HAVING clause e, since the query otherwise works perfectly?

Geoff_S
  • 4,917
  • 7
  • 43
  • 133
  • 1
    typically having is after group by. If not working after you move it, use a nested subquery, and pray the sql engine can do all the optimization right. – Bing Wang May 15 '22 at 00:21
  • 1
    Besides the syntax mistake of using HAVING before GROUP BY (this is not syntactically correct), it's not clear why you are using GROUP BY, because you are not using aggregation functions. You're just creating an invalid query. See my answer at [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e/13999903#13999903) – Bill Karwin May 15 '22 at 01:03
  • 1
    Having should be after Group BY – Amit Verma May 15 '22 at 03:08

0 Answers0