1

With two tables structured like this:

Table1                       Table2
----------------             ----------------
id, time, x                  id, time, y, z
1   1     1                  1   2     2  2
1   2     2                  1   3     3  3

I am trying to join the tables to get a result that looks like this:

Result
--------------------------------
id, time, x, y, z
1   1     1  
1   2     2  2  2
1   3        3  3

I was able to get a partial result with a query like this, but it is returning all of the values from table2 (including undesired ids) along with the joined values of table1.

select * 
from Table1 
full outer join Table2 on Table1.time=Table2.time and Table1.id ='1' and Table2.id ='1'
order by Table2.time

Does anyone know how to fix? I'm trying to run this query in AWS Athena/Prestodb if that matters.

Thanks for the help!

1

I don't use amazon-presto, but I'm wondering if you could adapt this SQL Server solution to your needs.

--demo setup
drop table if exists table1;
drop table if exists table2;
CREATE TABLE Table1
    (id int, time int, x int)
;

INSERT INTO Table1
    (id, time, x)
VALUES
    (1, 1, 1),
    (1, 2, 2)
;


CREATE TABLE Table2
    (id int, time int, y int, z int)
;

INSERT INTO Table2
    (id, time, y, z)
VALUES
    (1, 2, 2, 2),
    (1, 3, 3, 3)
;

------------------
--solution
;with cte as
(
select id, time from Table1
union
select id, time from Table2
)
SELECT c.id, c.TIME, t1.x, t2.y, t2.z
FROM cte c
LEFT JOIN table1 t1
    ON t1.id = c.id
        AND t1.TIME = c.TIME
LEFT JOIN table2 t2
    ON t2.id = c.id
        AND t2.TIME = c.TIME;

| id | TIME | x    | y    | z    |
|----|------|------|------|------|
| 1  | 1    | 1    | NULL | NULL |
| 1  | 2    | 2    | 2    | 2    |
| 1  | 3    | NULL | 3    | 3    |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.