How to join 2 SQL queries into a single one (one is an average value calculation and another search criteria with BETWEEN

Question

How to join 2 SQL queries into a single one (one is an average value calculation and another search criteria with BETWEEN

Navigation

#1 by (5 votes)

4

I am setting up a system that finds providers in a specific region, calculates the average amount charged by them and brings this value.

But before that he will only pick up the providers that are available on the requested date. I have these 2 queries done and separated, but I'm not getting a way to put both in a single.

Availability rule query:

SELECT *
FROM fcs_prestadores_pedidos
WHERE hora_entrada not BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
AND hora_saida not between '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'

Query with calculation of the average value within the 90km radius

SELECT avg(valor)
FROM (SELECT valor,(6371 * acos( cos( radians(-23.5427450) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-46.6513010) ) + sin( radians(-23.5427450) ) * sin( radians( lat ))))
AS distancia
FROM fcs_prestadores) virtual
WHERE distancia < 90

How can I first make the availability query, then calculate the average value?

mysql select

asked by anonymous 03.08.2017 / 15:32

1 answer

Pulling elements from one list to another under a criterion in R Schedule automatic updates in R

score 5 · Accepted Answer

Use the NOT EXISTS clause in the second query using the values of the first:

SELECT AVG(virtual.valor) AS valor,
       virtual.id
  FROM (SELECT fp.valor,
               (6371 * ACOS(COS(RADIANS(-23.5427450)) * COS(RADIANS(fp.lat)) * COS(RADIANS(fp.lng) - RADIANS(-46.6513010)) + SIN(RADIANS(-23.5427450)) * SIN(RADIANS(fp.lat)))) AS distancia,
               fp.id
          FROM fcs_prestadores fp) virtual
 WHERE virtual.distancia < 90
   AND NOT EXISTS(SELECT 1
                    FROM fcs_prestadores_pedidos fpp
                   WHERE fpp.prestador_id = virtual.id
                     AND (fpp.hora_entrada BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'
                      OR fpp.hora_saida BETWEEN '2017-08-10 16:00:00' AND '2017-08-10 18:00:00'))
 GROUP BY virtual.id

Changes made to query were:

Added id in return of table fcs_prestadores to be compared to fcs_prestadores_pedidos ;
% with% of% of provider to ensure that one line is displayed for each;
GROUP BY will check if there is a request for the provider determined in the desired interval. The link is done by the id column.