Query with HAVING and WHERE

Question

I'm trying to create a single query that will combine the following two queries.

SELECT 
  campgroundid, 
  ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
  AS distance 
FROM campground 
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

SELECT * FROM campground WHERE type='private' AND wifi = 1

I tried putting them into an IN but it returned a syntax error I couldn't figure out how to fix. I tried just removing the HAVING and combining the queries, but then it says it isn't able to figure out what distance is. Any help is appreciated. Thanks.

OUTPUT: [campgroundid, name, type, wifi, distance] [1,camp ABC, private, 1, 1.34 mi] [2,camp XYZ, private, 1, 4.44 mi]

Added some example output. Also fixed the one query to have the correct table name, sorry @Cybernate. — Seth, Jan 10 '11 at 02:24

Larry Lustig · Accepted Answer · 2011-01-10T02:40:12.203

3

Among the information not given is how the campground and markers tables are related. We'll need that info to know how to JOIN the tables.

Also, HAVING requires GROUP BY (it operates like a WHERE clause on the aggregated results of GROUP BY). If you're not aggregating the rows in markers, you want WHERE, not HAVING.

At a guess, you want something like this:

 SELECT id (expression) as distance FROM markers
      WHERE distance < 25 AND 
        campground_id IN (SELECT id FROM campgrounds WHERE type = 'private' AND wifi = 1)

EDIT: Reflecting the new info that there's only one table.

You cannot use column ALIASes in a WHERE clause. I'm guessing you know that, and also know that you can use them in HAVING, which is why you're trying to swap HAVING in place of WHERE. To do that, you'll have to rewrite as a GROUP BY query:

SELECT campgroundid, name, private, wifi, 
   ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
    cos( radians( lng ) - radians(-122) ) + 
    sin( radians(37) ) * sin( radians( lat ) ) ) ) 
    AS distance 
FROM campground 
GROUP BY campgroundid 
HAVING distance < 25 AND type='private' AND wifi = 1
ORDER BY distance LIMIT 0 , 20;

This will work as long as campgroundid is unique (since the other values will then come from the only record for this id).

edited Jan 10 '11 at 02:40

answered Jan 10 '11 at 02:24

Larry Lustig

49,320
14
110
160

I think type = 'private' AND wifi = 1 can go into main query – Chandu Jan 10 '11 at 02:28
Not in this version, they don't appear to be attributes of markers but rather of campgrounds. However, from the additional information provided it seems clear the final answer is going to involve a JOIN. The OP needs to provide additional information about how markers and campgrounds are related. – Larry Lustig Jan 10 '11 at 02:31
I'm sorry for the confusion, I miss wrote the expression query to have the wrong table name. Markers should actually be campground. So the two queries are directed towards the same table. – Seth Jan 10 '11 at 02:33
Also, when querying the table with the expression and utilizing the where distance, I get an invalid column name error on 'distance' – Seth Jan 10 '11 at 02:34
You can not use a column alias in a WHERE clause without a subquery. The HAVING is required to work. MySQL extends HAVING to add an implied subquery. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html – nate c Jan 10 '11 at 02:38

score 0 · Answer 2 · answered Feb 17 '14 at 12:27

0

SELECT campgroundid, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM campground WHERE type='private' AND wifi = 1 ORDER BY distance LIMIT 0 , 20 HAVING distance < 25

this may work

answered Feb 17 '14 at 12:27

Bhaskar

1

Can you please comment your solution? – user35443 Feb 17 '14 at 12:44

score 0 · Answer 3 · answered Jan 10 '11 at 02:23

0

It looks like it should be WHERE distance < 25, as HAVING is for queries such as HAVING MAX(distance) < 25 and other aggregate functions.

answered Jan 10 '11 at 02:23

SilverbackNet

2,076
17
29

When I run the query using 'WHERE distance', it gives me "Unknown Column 'distance' in 'Where clause' error. – Seth Jan 10 '11 at 02:30
1

`HAVING` is extended in MySQL to implicitly add a column alias that can use the `WHERE` clause as if it were a subquery. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html – nate c Jan 10 '11 at 02:35
Yes, it through me for a loop at first too. – nate c Jan 10 '11 at 02:44

score 0 · Answer 4 · answered Jan 10 '11 at 02:35

0

I guess this is worth to try (as simple as add the where clause from second sql into first)

SELECT 
  ... AS distance 
FROM campground 
WHERE type='private' AND wifi = 1
HAVING distance < 25 
ORDER BY distance LIMIT 0 , 20;

answered Jan 10 '11 at 02:35

ajreal

46,720
11
89
119

siddhusingh · Answer 5 · 2012-12-05T11:48:39.133

-1

If your question is to have WHERE clause from two tables with a JOIN logic. Then you must include that value in SELECT list. For e.g, SELECT USER.UserName, USER.UserId, LOC.id, LOC.lat, LOC.lon, ( 3959 * acos( cos( radians('123.1210022') ) * cos( radians( lat ) ) * cos( radians( lon ) - radians('21.200001') ) + sin( radians('123.1210022') ) * sin( radians( lat ) ) ) ) AS distance FROM userlocation LOC, user USER HAVING distance < '1' AND LOC.id = USER.UserId ORDER BY distance LIMIT 0 , 20

If you miss USER.UserId in Select list, you will not be able to LOC.id = USER.UserId in the WHERE clause.

edited Dec 05 '12 at 11:48

answered Dec 04 '12 at 13:54

siddhusingh

1,832
4
25
30

Please edit your answer and format the code to make it readable. – kleopatra Dec 04 '12 at 13:57

Query with HAVING and WHERE

5 Answers5