CODEWITHSUNDEEP
mysqlsql
edbird88
edbird88

Reputation: 128

Join 2 queries results

I have this query:

SELECT YEAR(`data`) AS ano, SUM(ativo) AS tempo_ativo
FROM rh.processamento
GROUP BY YEAR(`data`);

The result from this query is

ano tempo_ativo
2015 108247387
2016 172003845

And this query:

SELECT YEAR(`data`) AS ano, SUM(tempo) AS tempo_extra
FROM rh.aprovacoes
WHERE tipo = 'BH' OR tipo = 'HE' AND estado=1
GROUP BY YEAR(`data`);

The result is:

ano tempo_extra
0    8768100
2015  -4410782
2016  -7213369

I made this query to join the results from both queries:

SELECT YEAR(processamento.`data`) AS ano, SUM(ativo) AS tempo_ativo, SUM(tempo)/3600 AS tempo_extra
FROM rh.processamento
LEFT JOIN rh.aprovacoes ON processamento.`data`=aprovacoes.`data` AND (tipo = 'BH' OR tipo = 'HE') AND estado=1
GROUP BY YEAR(aprovacoes.`data`);

But the results are wrong. I need the results to be something like this:

ano tempo_ativo tempo_extra
0    NULL       8768100
2015 108247387  -4410782
2016 172003845  -7213369

Can you you guys help me?

Upvotes: 1

Views: 48

Answers (5)

maximilienAndile
maximilienAndile

Reputation: 202

In order to have all the "ano" that are listed in your aprovacoes table even if there is no corrsponding "ano" rows in your processamento table you should make the left join like this :

SELECT * FROM rh.aprovacoes LEFT JOIN rh.processamento ON processamento.ano=aprovacoes.ano AND ....

You can after that make your GROUP BY YEAR(aprovacoes.data)

For more info about the LEFT JOIN Clause look at this article : http://www.w3schools.com/sql/sql_join_left.asp

I hope it will help you.

Upvotes: 0

pancho018
pancho018

Reputation: 587

SELECT YEAR(processamento.`data`) AS ano, SUM(ativo) AS tempo_ativo, SUM(tempo)/3600 AS tempo_extra
FROM rh.processamento
FULL OUTER JOIN rh.aprovacoes ON processamento.`data`=aprovacoes.`data` AND (tipo = 'BH' OR tipo = 'HE') AND estado=1
GROUP BY YEAR(aprovacoes.`data`);

left join will only return a row if it exists in the first table. full join will return rows from both tables.

but since you cant do full outer joins in mySQL you have to emulate it.

Full Outer Join in MySQL so do the left join, then do the right join and union the 2.

    SELECT YEAR(processamento.`data`) AS ano, SUM(ativo) AS tempo_ativo, SUM(tempo)/3600 AS tempo_extra
    FROM rh.processamento
    Left JOIN rh.aprovacoes ON processamento.`data`=aprovacoes.`data` AND (tipo = 'BH' OR tipo = 'HE') AND estado=1
    GROUP BY YEAR(aprovacoes.`data`);

union

    SELECT YEAR(processamento.`data`) AS ano, SUM(ativo) AS tempo_ativo, SUM(tempo)/3600 AS tempo_extra
    FROM rh.processamento
    Right JOIN rh.aprovacoes ON processamento.`data`=aprovacoes.`data` AND (tipo = 'BH' OR tipo = 'HE') AND estado=1
    GROUP BY YEAR(aprovacoes.`data`);

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

SELECT YEAR(`a`,`data`) AS ano, SUM(tempo) AS tempo_extra,
SUM(ativo) AS tempo_ativo
FROM rh.aprovacoes a LEFT JOIN rh.processamento b 
ON YEAR(`a`.`data`) = YEAR(`b`.`data`)
AND estado=1
WHERE tipo = 'BH' OR tipo = 'HE' 
GROUP BY YEAR(`a`.`data`);

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

If the second query always returns all records of the first one, then you can try using a LEFT JOIN:

SELECT t1.ano, tempo_extra, tempo_ativo
FROM (
   SELECT YEAR(`data`) AS ano, SUM(tempo) AS tempo_extra
   FROM rh.aprovacoes
   WHERE tipo = 'BH' OR tipo = 'HE' AND estado=1
   GROUP BY YEAR(`data`)
) AS t1
LEFT JOIN (
   SELECT YEAR(`data`) AS ano, SUM(ativo) AS tempo_ativo
   FROM rh.processamento
   GROUP BY YEAR(`data`) 
) AS t2 ON t1.ano = t2.ano

Upvotes: 1

Dylan Su
Dylan Su

Reputation: 6065

Try this:

SELECT  *
FROM 
(
    SELECT YEAR(`data`) AS ano, SUM(tempo) AS tempo_extra
    FROM rh.aprovacoes
    WHERE tipo = 'BH' OR tipo = 'HE' AND estado=1
    GROUP BY YEAR(`data`)
) t1
LEFT JOIN
(
    SELECT YEAR(`data`) AS ano, SUM(ativo) AS tempo_ativo
    FROM rh.processamento
    GROUP BY YEAR(`data`)
) t2
USING(ano)

Upvotes: 0

Related Questions