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_, Apr 14 '15 at 07: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, Apr 14 '15 at 09: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, Apr 14 '15 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, Apr 14 '15 at 16:48

score 1 · Accepted Answer · edited May 23 '17 at 11:57

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.

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? — NASA Intern, Apr 14 '15 at 15:07
I edited the query in the post with `<>''` checks, the [where clause](https://dev.mysql.com/doc/refman/5.7/en/select.html) '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. — lp_, Apr 14 '15 at 16:05
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, Apr 14 '15 at 16:16
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... — NASA Intern, Apr 14 '15 at 16:26
sorry, that was my fault. you cannot use column aliases in where clause, this is the reason why it gave you the error. I edited the post for a little explanation. thanks for the feedback. — lp_, Apr 14 '15 at 18:07

Combining Multiple Mysql Queries

1 Answers1