Help with MYSQL query

Posted on 2012-09-13
Medium Priority
Last Modified: 2012-09-13

I've currently got a query that returns results from a database using their longitude/latitude.

SELECT s.*, 6371.04 * acos( cos( pi( ) /2 - radians( 90 - latitude) ) * cos( pi( ) /2 - radians( 90 - '$lat' ) ) * cos( radians( longitude) - radians( '$lon' ) ) + sin( pi( ) /2 - radians( 90 - latitude) ) * sin( pi( ) /2 - radians( 90 - '$lat' ) ) ) AS distance, a.average_rating FROM cms_salon s LEFT JOIN (SELECT ROUND(AVG(rating)) AS average_rating, salon_id FROM comments GROUP BY salon_id) a on a.salon_id = s.salon_id WHERE s.salon_id > 0 OR s.salon_name LIKE '%$keyword%' GROUP BY s.salon_id HAVING distance < '$radius' ORDER BY distance ASC

I've recently been asked to amend the query so that it searches by both location and name. I've added the bit in bold but it doesn't really work well.

I know what I need to do, but I don't know how to do it. I think what I need is to be able to perform an 'OR' query on the distance and salon name.

For example..

WHERE distance < '20' OR s.salon_name LIKE '%some name%'

However I can't use distance within the WHERE, it only works using HAVING.

Can anyone help?
Question by:SheppardDigital
  • 2
  • 2
LVL 24

Expert Comment

ID: 38394010
Do two queries for the two different criteria and just combine them using UNION.
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38394031
The 'or' should work in the having too ... try
    HAVING distance < '$radius' OR s.salon_name LIKE '%some name%'

Author Comment

ID: 38394033
The issue I have with that is that the location based query has one additional column (distance)

Author Closing Comment

ID: 38394036
Yep, it does.

Thank you.
LVL 24

Expert Comment

ID: 38394082
Hmmm didn't know MySQL allowed that.  Just note that as stated in http://dev.mysql.com/doc/refman/5.5/en/select.html, it is a non-standard extension done by MySQL.  The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question