CODEWITHSUNDEEP
mysqlsql
Preethy
Preethy

Reputation: 722

Sql query combining

i have two sql queries those are following

1) SELECT a.* FROM modzzz_listing_main as a LEFT JOIN modzzz_listing_rating as b ON a.id=b.gal_id WHERE LTRIM(a.city) = 'Houston' AND a.state = 'TX' AND a.tags LIKE '%Barber Shop%' ORDER BY b.gal_rating_sum DESC LIMIT 0 ,10

2) SELECT zip_code ,( 3959 * acos( cos( radians('41.97734070') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-70.97234344') ) + sin( radians('41.97734070') ) * sin( radians( latitude ) ) ) ) AS distance FROM city_finder WHERE latitude IS NOT NULL AND longitude IS NOT NULL HAVING distance < 20 ORDER BY distance ASC

how can i combine this two queries by the condition `

modzzz_listing_main.zip=city_finder.zip_code

` .i am totally confused..please any one help me..

Upvotes: 0

Views: 55

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

to see the join easier:

select * from
(
SELECT a.* FROM modzzz_listing_main as a LEFT JOIN modzzz_listing_rating as b ON a.id=b.gal_id WHERE LTRIM(a.city) = 'Houston' AND a.state = 'TX' AND a.tags LIKE '%Barber Shop%' ORDER BY b.gal_rating_sum DESC LIMIT 0 ,10
) queryA
left join
(
SELECT zip_code ,( 3959 * acos( cos( radians('41.97734070') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('-70.97234344') ) + sin( radians('41.97734070') ) * sin( radians( latitude ) ) ) ) AS distance FROM city_finder WHERE latitude IS NOT NULL AND longitude IS NOT NULL HAVING distance < 20 ORDER BY distance ASC
) queryB
on queryA.zip=queryB.zip_code

proper formatting

SELECT *
FROM
  ( SELECT a.*
   FROM modzzz_listing_main AS a
   LEFT JOIN modzzz_listing_rating AS b ON a.id=b.gal_id
   WHERE LTRIM(a.city) = 'Houston'
     AND a.state = 'TX'
     AND a.tags LIKE '%Barber Shop%'
   ORDER BY b.gal_rating_sum DESC LIMIT 0 ,
                                        10 ) queryA
LEFT JOIN
  ( SELECT zip_code ,
           (3959 * acos(cos(radians('41.97734070')) * cos(radians(latitude)) * cos(radians(longitude) - radians('-70.97234344')) + sin(radians('41.97734070')) * sin(radians(latitude)))) AS distance
   FROM city_finder
   WHERE latitude IS NOT NULL
     AND longitude IS NOT NULL HAVING distance < 20
   ORDER BY distance ASC ) queryB ON queryA.zip=queryB.zip_code

Upvotes: 1

Related Questions