CODEWITHSUNDEEP
mysqlsqlgeospatialgeo
Mic
Mic

Reputation: 427

Merging 2 sql queries into a single one

I have two sql queries which when run independent produces the correct results

Query 1

SELECT id,
(6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) ) ) 
            AS distance 
            FROM geodata HAVING distance < 20 
            ORDER BY distance 
            LIMIT 0 , 20;

Query 2

SELECT DISTINCT e.* FROM schools e  
WHERE (
    (e.type = 'preprimary')
) 
AND(
    e.title LIKE '%government%' 
)   
LIMIT 0, 10

I want to merge the first query with the second one, so that it should return all "preprimary" type schools with title like "government" located within 20KM radius and the result needs to be ordered by the distance.

How can I merge the two queries? I tried using JOINING the geodata table on the school table. But I dont know the remaining. Sorry, if this is a silly question. I am pretty new to SQL world.

Upvotes: 1

Views: 67

Answers (2)

Oscar P&#233;rez
Oscar P&#233;rez

Reputation: 4397

Try this:

 SELECT *
   From (
 SELECT DISTINCT e.* ,
       (6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) )
        ) as distance
  FROM schools e  
LEFT JOIN geodata g ON e.id=g.id
 WHERE (e.type = 'preprimary')
   AND ( e.title LIKE '%government%' )
 ) as s
  Where s.distance  < 20
  Order by s.distance

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT DISTINCT school.* FROM
 ( SELECT geodata.id,
(6371 * acos( cos( radians(9.977364864079215) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(76.58620953448485) ) + sin( radians(9.977364864079215) ) * sin( radians( latitude ) ) ) ) 
        AS distance ,school.*
        FROM geodata LEFT JOIN school on geodata.id=school.id
        WHERE
         (school.type = 'preprimary')
        AND(
         school.title LIKE '%government%' 
         )   
        AND school.id IS NOT NULL
        HAVING distance < 20 )x
 ORDER BY x.distance 
        LIMIT 0 , 10;

Upvotes: 2

Related Questions