Teddybugs Teddybugs - 2 months ago 8
MySQL Question

Mysql: Select 2 table and display it to column based on year

I have my table look like this:

tableA:

YEAR | GenerationA | TransmissionA
--------------------------------
2015 | 213 | null
2016 | 1122 | null
2016 | null | 5
2017 | null | 999


tableB:

YEAR | GenerationB | TransmissionB
--------------------------------
2014 | 111 | null
2016 | 534 | null
2016 | null | 4
2017 | null | 5
2017 | 555 | null


for both table A and B, the data will appear on either at Generation or Transmission column, it cannot be both column.

example, TableA, Year 2016, 1st data appear at column Generation for 534, 2nd data for 2016 will appear at column Transmission for value 4.
some of it will appear once at Generation or Transmission

Result

i would like to join this 2 table based on year, so that it become result like below:

YEAR | GenerationA | TransmissionA | GenerationB | TransmissionB
----------------------------------------------------------------------------------------
2014 | null | null | 111 | null
2015 | 213 | null | null | null
2016 | 1122 | 5 | 534 | 4
2017 | null | 999 | 555 | 5


Schema:

i tried to create it on sqlfiddle, it keep give me error, so here is it:

CREATE TABLE tableA
(
Year int(11),
GenerationA int(20),
TransmissionA int(20)
);

CREATE TABLE tableB
(
Year int(11),
GenerationB int(20),
TransmissionB int(20)
);

INSERT INTO tableA VALUES (2015,213,null);
INSERT INTO tableA VALUES (2016,1122,null);
INSERT INTO tableA VALUES (2016,null,5);
INSERT INTO tableA VALUES (2017,null,999);

INSERT INTO tableB VALUES (2014,111,null);
INSERT INTO tableB VALUES (2016,534,null);
INSERT INTO tableB VALUES (2016,null,4);
INSERT INTO tableB VALUES (2017,null,5);
INSERT INTO tableB VALUES (2017,555,null);


SQL Approach:

SELECT * FROM tableA
LEFT JOIN tableB ON tableA.YEAR = tableB.YEAR
GROUP BY tableA.YEAR
UNION
SELECT * FROM tableA
RIGHT JOIN tableB ON tableA.YEAR = tableB.YEAR
GROUP BY tableB.YEAR


tried above sql, but cant get the result. Any help would be appreciated.

Answer

You can do this with union all and group by:

select year,
       max(GenerationA) as GenerationA,
       max(TransmissionA) as TransmissionA,
       max(GenerationB) as GenerationB,
       max(TransmissionB) as TransmissionB
from ((select year, GenerationA, TransmissionA, 
              NULL as GenerationB, NULL as TransmissionB
       from tableA
      ) union all
      (select year, NULL as GenerationA, NULL as TransmissionA, 
              GenerationB, TransmissionB
       from tableB
      )
     ) ab
group by year;