Combine three columns from different tables into one row

Question

I am new to sql and are trying to combine a column value from three different tables and combine to one row in DB2 Warehouse on Cloud. Each table consists of only one row and unique column name. So what I want to is just join these three to one row their original column names.

Each table is built from a statement that looks like this:

SELECT SUM(FUEL_TEMP.FUEL_MLAD_VALUE) AS FUEL 
FROM
    (SELECT ML_ANOMALY_DETECTION.MLAD_METRIC AS MLAD_METRIC, ML_ANOMALY_DETECTION.MLAD_VALUE AS FUEL_MLAD_VALUE, ML_ANOMALY_DETECTION.TAG_NAME AS TAG_NAME, ML_ANOMALY_DETECTION.DATETIME AS DATETIME, DATA_CONFIG.SYSTEM_NAME AS SYSTEM_NAME
     FROM ML_ANOMALY_DETECTION 
         INNER JOIN DATA_CONFIG ON 
               (ML_ANOMALY_DETECTION.TAG_NAME  =DATA_CONFIG.TAG_NAME AND 
                DATA_CONFIG.SYSTEM_NAME = 'FUEL') 
     WHERE ML_ANOMALY_DETECTION.MLAD_METRIC = 'IFOREST_SCORE'
       AND ML_ANOMALY_DETECTION.DATETIME >= (CURRENT DATE - 9 DAYS) 
     ORDER BY DATETIME DESC)
AS FUEL_TEMP

I have tried JOIN, INNER JOIN, UNION/UNION ALL, but can't get it to work as it should. How can I do this?

Reading https://stackoverflow.com/help/mcve is a good start when asking qeustions. — jarlh, Jan 26 '18 at 12:08
is the query above an example of 1 table query that works, and you basically need to do this 3 times, and turn it into 1 view? — Crezzer7, Jan 26 '18 at 12:16
Yes it is one table-query which I want to be one of the columns in a resulting table. So yes, I want to combine three of those to a table with three column and one row. @Crezzer7 — danielo, Jan 26 '18 at 12:19
ok, what are the 3 tables called, and what are the columns you want? — Crezzer7, Jan 26 '18 at 12:24
So, you have 3 table with 1 row in each of number. And you want to sum all these 3 values. You could try to union it into single table with 3 row and then summ all values. table 1 union all table 2 — Vasiliy Zverev, Jan 26 '18 at 12:31
have one table with a column name "Fuel" and one row, one with "Boiler" and one row, and lastly one with "Residue" and one row. These three tables has one one row and one column each with different names which I want to combine to one table with one row, and three columns accordingly to their names. — danielo, Jan 26 '18 at 12:52

score 1 · Answer 1 · answered Jan 26 '18 at 12:36

Using UNION should solve your problem. Something like this:

SELECT
  WarehouseDB1.WarehouseID AS TheID,
  'A' AS TheSystem,
  WarehouseDB1.TheValue AS TheValue
FROM WarehouseDB1
UNION
SELECT
  WarehouseDB2.WarehouseID AS TheID,
  'B' AS TheSystem,
  WarehouseDB2.TheValue AS TheValue
FROM WarehouseDB2
UNION
  WarehouseDB3.WarehouseID AS TheID,
  'C' AS TheSystem,
  WarehouseDB3.TheValue AS TheValue
FROM WarehouseDB3

Ill adapt the code with your table names and rows if you tell me what they are. This kind of query would return something like the following:

TheID   TheSystem   TheValue
  1         A          10
  2         A          20
  3         B          30
  4         C          40
  5         C          50

As long as your column names match in each query, you should get the desired results.

This does not solve the problem. I have one table with a column name "Fuel" and one row, one with "Boiler" and one row, and lastly one with "Residue" and one row. These three tables has one one row and one column each with different names which I want to combine to one table with one row, and three columns accordingly to their names. — danielo, Jan 26 '18 at 12:52
ahh ok I get you now, I think you may need to union the tables together, so the data is all in one place, and then transpose it... maybe this will help: https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Crezzer7, Jan 26 '18 at 13:00

score 1 · Accepted Answer · answered Jan 26 '18 at 19:30

Use a cross-join like this:

create table table1 (field1 char(10));
create table table2 (field2 char(10));
create table table3 (field3 char(10));

insert into table1 values('value1');
insert into table2 values('value2');
insert into table3 values('value3');

select *
  from table1
  cross join table2
  cross join table3;

Result:

field1     field2     field3
---------- ---------- ----------
value1     value2     value3

A cross join joins all the rows on the left with all the rows on the right. You will end up with a product of rows (table1 rows x table2 rows x table3 rows). Since each table only has one row, you will get (1 x 1 x 1) = 1 row.

Combine three columns from different tables into one row

2 Answers2