Help with MYSQL query

Posted on 2012-09-13
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
    LVL 24

    Expert Comment

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

    Accepted Solution

    The 'or' should work in the having too ... try
        HAVING distance < '$radius' OR s.salon_name LIKE '%some name%'

    Author Comment

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

    Author Closing Comment

    Yep, it does.

    Thank you.
    LVL 24

    Expert Comment

    Hmmm didn't know MySQL allowed that.  Just note that as stated in, 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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now