CODEWITHSUNDEEP
sqlmysqlsubqueryin-subquery
Seth
Seth

Reputation: 679

Query with HAVING and WHERE

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]

Upvotes: 3

Views: 10733

Answers (5)

Bhaskar
Bhaskar

Reputation: 1

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

Upvotes: 0

siddhusingh
siddhusingh

Reputation: 1868

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.

Upvotes: -1

Larry Lustig
Larry Lustig

Reputation: 50970

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

Upvotes: 3

ajreal
ajreal

Reputation: 47321

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;

Upvotes: 0

SilverbackNet
SilverbackNet

Reputation: 2074

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

Upvotes: 0

Related Questions