CODEWITHSUNDEEP
sql
Emil Nolin
Emil Nolin

Reputation: 72

SQL nested union

I have two tables with some ID's and I need a query that returns the following:

new_bet_id  old_bet_id  new_id  old_id
110         120         1       2
130         140         3       4
150         160         5       6

I'm having no problem getting new_bet_id correct, its the old_bet_id i,m having problem with since its from the same column. The new_id will me mapped with new_bet_id and old_id will be mapped with old_bet_id

So far I've tried the following:

SELECT 
one.bet_id as new_bet_id,
null as old_bet_id,
two.new_id,
two.old_id

FROM test_table_two two 
JOIN test_table_one one ON one.kund_id = two.new_id

UNION

SELECT
null  as new_bet_id,
one.bet_id as old_bet_id,
two.new_id,
two.old_id

FROM test_table_two two 
JOIN test_table_one one ON one.kund_id = two.old_id

But the result isn't really what i'm looking for:

new_bet_id  old_bet_id  new_id  old_id
110         null        1       2
130         null        3       4
150         null        5       6
null        120         1       2
null        140         3       4
null        160         5       6

Below is the database setup for this example

CREATE TABLE test_table_one
(bet_id int,
kund_id int);

CREATE TABLE test_table_two
(new_id int,
old_id int);

insert into test_table_one values(110,1);
insert into test_table_one values(120,2);
insert into test_table_one values(130,3);
insert into test_table_one values(140,4);
insert into test_table_one values(150,5);
insert into test_table_one values(160,6);

insert into test_table_two values(1,2);
insert into test_table_two values(3,4);
insert into test_table_two values(5,6);

Hope someone can figure this out, Thanks

Upvotes: 3

Views: 1177

Answers (2)

Amy B
Amy B

Reputation: 110071

UNION gives you more rows. If you don't want more rows, it's the wrong tool.

SELECT
  oneA.bet_id as new_bet_id,
  oneB.bet_id as old_bet_id,
  two.new_id,
  two.old_id
FROM test_table_two two
  JOIN test_table_one oneA ON two.new_id = oneA.kund_id
  JOIN test_table_one oneB ON two.old_id = oneB.kund_id

Upvotes: 4

Manuel
Manuel

Reputation: 10303

I think this can be done with a LEFT OUTER JOIN(or RIGHT depending on the situation) like this:

    SELECT 
    one.bet_id as new_bet_id,
    null as old_bet_id,
    two.new_id,
    two.old_id

    FROM test_table_two two 
    LEFT OUTER JOIN test_table_one one ON one.kund_id = two.new_id

Upvotes: 0

Related Questions