Combining Multiple Mysql Queries

Question

I have the following table:

id  user_id  recorded      latitude      longitude   speed  note_type  details  image_url                           
1   10      3/29/2013    33.77701316   -84.39004377   -1       11       Test     2ecc2e36c3e1a512d349f9b407fb281e-2013-03-29-16-15-..

I am trying to find a way to combine the following queries into one complete query that would give me all records that match each individual query (each of these works fine separately just cant figure out how to combine them):

SELECT id, ( 3959 * acos( cos( radians(User_Input_Longitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(User_Input_Latitude) ) + sin( radians(User_Input_Longitude) ) * sin( radians( latitude ) ) ) ) AS distance 
FROM note HAVING distance < User_Input_Distance 
ORDER BY distance LIMIT 0 , 1000

SELECT details 
FROM note 
WHERE CHAR_LENGTH(details) > User_Input_CharacterLength

SELECT DISTINCT details 
FROM note;

SELECT DISTINCT image_url 
FROM note;

So basically I need a query that compares the distance based on the long/lat points to user defined long/lat points and user defined distance, checks the character length of the details field and then finally only records that have distinct data for details and image_urls (alot of the image_urls and details are left empty so I've been using distinct to find only those that actually have data in them not sure if this is proper way to do ti).

Like I said before, each of these queries works individually right now but unfortunately I dont have enough skillz in mySql to combine them in an intelligent way.

Any advice on this would be great.

HAVING distance < User_Input_Distance should be WHERE distance < User_Input_Distance, for starters. other than that you need to update with your table schema as you need to perform some joins, and for that the relationships need to be known. — pala_
– pala_, Commented Apr 14, 2015 at 7:02
The answer given by @lp_ covers the correct approach to structuring the MySQL query. You may also want to consider simplifying the distance clause, and simply select a (potentially) slightly larger result set for the 'bounding square', then in your PHP reject the results that don't meet the more exact polar coordinates. That way you reduce CPU load, and can also take advantage of indexes on the longitude and latitude columns, especially if your table is large. HTH — Gavin Jackson
– Gavin Jackson, Commented Apr 14, 2015 at 9:35
@GavinJackson Good point. Just a bit of clarification: you say make a simpler query for checking distance that would basically take a larger area then in my php file filter closer to reduce load. Makes sense as I figured this query would be computationally expensive. My table have about 1k entries but what do you mean by "take advantage of indexes on the longitude and latitude columns" ? — NASA Intern
– NASA Intern, Commented Apr 14, 2015 at 15:23
@NASAIntern At the moment I think the query would need to calculate your distance for every row, since without doing the polar computations it can't know which rows will qualify in the intermediate HAVING clause. If you make the SELECT based on the 'bounding square', by pre-calculating a max_lat, min_lat, max_lng, min_lng, as a function of 'User_Latitude', 'User_Longitude' and 'User_Distance', then a WHERE clause of (latitude BETWEEN (min_lat AND max_lat))AND(longitude BETWEEN min_lng AND max_lng ) would only need to read qualifying rows if latitude and longitude columns were indexed. — Gavin Jackson
– Gavin Jackson, Commented Apr 14, 2015 at 16:48

Community · Accepted Answer · 2017-05-23 11:57:03Z

1

Although the schema is not provided, it seems that there is only one table. Maybe this is what you just want to do:

SELECT id, ( 3959 * acos( cos( radians(User_Input_Longitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(User_Input_Latitude) ) + sin( radians(User_Input_Longitude) ) * sin( radians( latitude ) ) ) ) AS distance, details, image_url
FROM note
WHERE ( 3959 * acos( cos( radians(User_Input_Longitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(User_Input_Latitude) ) + sin( radians(User_Input_Longitude) ) * sin( radians( latitude ) ) ) ) < User_Input_Distance
  AND CHAR_LENGTH(details) > User_Input_CharacterLength
  AND (details IS NOT NULL AND details<>'')
  AND (image_url IS NOT NULL AND image_url<>'')
ORDER BY distance LIMIT 0, 1000

Some notes:

The use of having clause without group by clause is not really a nice way to use the conditions in your query, normally, you should use where instead (see below).
You say, that a lot of image_url and details are empty, you can use is not null to check if they are NULL or not. If you want to filter empty strings too, add e.g. details<>'' and image_url<>'' to the where conditions.
This query will combine all your criterias, so it will select only those records where all of them are matching, if this is not what you want, you can use or to have disjunctions of some of the conditions instead of their conjunctions.

[edit]

MySQL (as standard SQL too) disallows references to column aliases in a WHERE clause (see the manual). So you either repeat the full expression to calculate distance in it or use HAVING clause (which should produce the same result here, but in a slightly different way).

SELECT id, ( 3959 * acos( cos( radians(User_Input_Longitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(User_Input_Latitude) ) + sin( radians(User_Input_Longitude) ) * sin( radians( latitude ) ) ) ) AS distance, details, image_url
FROM note
HAVING distance < User_Input_Distance
  AND CHAR_LENGTH(details) > User_Input_CharacterLength
  AND (details IS NOT NULL AND details<>'')
  AND (image_url IS NOT NULL AND image_url<>'')
ORDER BY distance LIMIT 0, 1000

It works, because in HAVING you can refer to aliases, but using it without a GROUP BY, is not really a proper use of SQL and it might affect performance too. You can read more about having vs. where here or here.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Apr 14, 2015 at 8:00

lp_

1,1781 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

NASA Intern Over a year ago

Hey thanks, this looks good. And yeah its all from one table so i dont think i need to do any joins. Also NOT NULL didnt work because of the empty strings. So you are saying to add the (and details<>'') as a separate statement or just combine with the same line as NOT NULL? Also in this situation does it matter what order the queries appear in? What would happen if the distance came 3rd or last? Is it basically filtering one after the other when its written like this?

lp_ Over a year ago

I edited the query in the post with <>'' checks, the where clause 'indicates the condition or conditions that rows must satisfy to be selected', so if you specify there more conjunctive conditions, the RDBMS will select those records which satisfy all of them.

NASA Intern Over a year ago

Hey I get an error on the WHERE distance line. Its saying distance isnt a column name which is true. I removed it and everything else works fine.

NASA Intern Over a year ago

"#1054 - Unknown column 'distance' in 'where clause'"

NASA Intern Over a year ago

I changed the WHERE distance to HAVING distance and it seems to work. not sure why I need to do some more reading on how these commands function. I have a very basic level of understanding with mysql that t needs to be upgraded...

|

Collectives™ on Stack Overflow

Combining Multiple Mysql Queries

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related