Help with MYSQL query

Hi,

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?
SheppardDigitalAsked:
Who is Participating?
 
lwadwellCommented:
The 'or' should work in the having too ... try
    HAVING distance < '$radius' OR s.salon_name LIKE '%some name%'
0
 
johanntagleCommented:
Do two queries for the two different criteria and just combine them using UNION.
0
 
SheppardDigitalAuthor Commented:
The issue I have with that is that the location based query has one additional column (distance)
0
 
SheppardDigitalAuthor Commented:
Yep, it does.

Thank you.
0
 
johanntagleCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.