Problema com INNER JOIN e UNION
09/04/2018
0
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
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
Curtir tópico
+ 0
Responder
Posts
10/04/2018
Jerson Boer
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
Responder
10/04/2018
Ilano Frota
Porque não fazer uma subquery? Tenta assim:
Espero ter ajudado.
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.descriptionEspero ter ajudado.
Responder
12/04/2018
Emerson Nascimento
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
Responder
Clique aqui para fazer login e interagir na Comunidade :)