CODEWITHSUNDEEP
phpmysql
NASA Intern
NASA Intern

Reputation: 853

Combining Multiple Mysql Queries

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.

Upvotes: 0

Views: 85

Answers (1)

lp_
lp_

Reputation: 1178

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.

Upvotes: 1

Related Questions