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]

Answer 1

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).