Join 2 queries results

Question

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?

You can use right join if you want extra column from second table — Siraj ul Haq, Mar 11 '16 at 11:13
In order for a `LEFT JOIN` to work as expected in this situation, the table you put on the left must contain in the column you use to join (`data`) **all** the values that are present in the join column in the right table. — axiac, Mar 11 '16 at 11:15

score 1 · Accepted Answer · answered Mar 11 '16 at 11:17

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

score 0 · Answer 2 · answered Mar 11 '16 at 11:15

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)

score 0 · Answer 3 · edited May 23 '17 at 12:23

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`);

Unfortunately, there is [no support for `FULL OUTER JOIN`](http://dev.mysql.com/doc/refman/5.7/en/join.html) in `MySQL` yet. — axiac, Mar 11 '16 at 11:18

score 0 · Answer 4 · answered Mar 11 '16 at 11:17

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`);

score 0 · Answer 5 · answered Mar 11 '16 at 11:22

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.

Join 2 queries results

5 Answers5