CODEWITHSUNDEEP
mysqlsql
Zaki Aziz
Zaki Aziz

Reputation: 3862

How to Combine 2 complex SQL queries into 1

I have a database full of users that roughly looks like this:

| id | username | birthdate  | sex | zip_code | latitude  | longitude  |
|----+----------+------------+-----+----------|-----------+------------|
|  1 |  userA   | 1986-04-05 |  M  |  90210   | 34.088808 | -118.40612 |
|  2 |  userB   | 1984-08-15 |  F  |  10011   | 40.741012 | -74.00012  |
|  3 |  userC   | 1984-11-25 |  F  |  10014   | 40.734718 | -74.00532  |

I have searched the internet and found 2 very helpful queries that allow me to calculate the distance and age of a particular user. Here is the distance query I have come up with:

SELECT username, (
(
ACOS( SIN( $latitude * PI( ) /180 ) * SIN( latitude * PI( ) /180 ) + COS( $latitude * PI( ) /180 ) * COS( latitude * PI( ) /180 ) * COS( (
$longitude - longitude
) * PI( ) /180 ) ) *180 / PI( )
) *60 * 1.1515
) AS  `distance` 
FROM  `userList` 
HAVING  `distance` <=15
ORDER BY  `distance` ASC 
LIMIT 0 , 30

This gives me a result of all the users in my database that is located 15 miles or less from me. Here is the age query:

    SELECT username, YEAR( CURDATE( ) ) - YEAR( birthdate ) - IF( STR_TO_DATE( CONCAT( YEAR( CURDATE( ) ) ,  '-', MONTH( birthdate ) ,  '-', IF( MONTH( birthdate ) =2
AND DAY( birthdate ) =29, DAY( DATE_ADD( CONCAT( YEAR( CURDATE( ) ) ,  '-03-01' ) , INTERVAL -1
DAY ) ) , DAY( birthdate ) ) ) ,  '%Y-%c-%e' ) > CURDATE( ) , 1, 0 ) AS age
FROM userList
LIMIT 0 , 30

What I would like to do is combine these queries in such a way where a user can search for other users that are located less than X miles away AND older than Y but younger then Z

How can I go about combining these queries into one? I'm not very well traversed in SQL queries and don't know which way to proceed.

Upvotes: 0

Views: 195

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

Select 
  username,
  (age_stuff) as age,
  (distance_stuff) as distance
from userList
where 
  distance < 15
  and age < 24 and age > 64
order by distance
limit 30

Upvotes: 2

Related Questions