Problema com INNER JOIN e UNION

MySQL

09/04/2018

Boa tarde.
gostaria de uma ajuda de vcs!
tenho um select onde ele consulta 2 tabelas uma ele conta as linhas e pega todos com o numero 3 e na outra pega o nome porem gostaria de ajuntar e somar tudo.
segue meu select.
SELECT s.id_route,s.description,COUNT(c.route_type) as route_type, count(if( c.state=3, state, null)) as state FROM currentcalls_whosale as c INNER JOIN gateways as s ON c.id_route=s.id_route GROUP BY id_route UNION ALL SELECT s.id_route,s.description,COUNT(d.route_type) as route_type, count(if( d.state=3, state, null)) as state FROM currentcalls_retail as d INNER JOIN gateways as s ON d.id_route=s.id_route GROUP BY id_route ORDER BY description

o Resultado sai assim

id_route - description - route_type - state
250 teste 25 7
250 teste 3 1

eu preciso q sai tudo junto
id_route - description - route_type - state
250 teste 28 8
Renato

Renato

Curtidas 0

Respostas

Jerson Boer

Jerson Boer

09/04/2018

Olá Renato, tente fazer a sua consulta colocando como principal a tabela de gateways e depois na mesma consulta você relaciona com as tabelas currentcalls_whosale e currentcalls_retail sem fazer Union, algo como:

Select * from gateways left join
currentcalls_whosale on currentcalls_whosale.id_route = gateways.id_route left join
currentcalls_retail on currentcalls_retail.id_route = gateways.id_route





GOSTEI 0
Ilano Frota

Ilano Frota

09/04/2018

Porque não fazer uma subquery? Tenta assim:

SELECT A.id_route, A.description,

      IFNULL((SELECT COUNT(route_type) FROM currentcalls_whosale X
      WHERE X.id_route = A.id_route), 0) AS TOTAL

FROM gateways A
ORDER BY A.description


Espero ter ajudado.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

09/04/2018

Renato, não conheço muito bem os recursos do MySQL, mas talvez esta consulta resolva:

SELECT
 s.id_route, s.description,
 count(*) as route_type,
 sum(if(coalesce(c.state, d.state, 0)=3,1,0)) as state
FROM
 gateways as s
LEFT JOIN
 currentcalls_whosale as c ON c.id_route=s.id_route
LEFT JOIN
 currentcalls_retail as d ON d.id_route=s.id_route
GROUP BY s.id_route, s.description
ORDER BY s.description

GOSTEI 0
POSTAR