Geek asks and answers

Flat Join two tables

For these two tables:

CREATE TABLE TEST1 ( ID  INTEGER ,key  VARCHAR(50),VALUE1 VARCHAR(50));
CREATE TABLE TEST2 ( ID  INTEGER ,key  VARCHAR(50),VALUE2 VARCHAR(50));

Table1:

130,    'HANSEN',   'STREET1'
130,    'HANSEN',   'STREET2'
130,    'HANSEN',   'STREET3'
120,    'HANSEN',   'STREET5'
120,    'HANSEN',   'STREET6'

Table 2:

130,    'HANSEN',   'ZIP1'
130,    'HANSEN',   'ZIP2'
130,    'HANSEN',   'ZIP3'
120,    'HANSEN',   'ZIP4'
120,    'HANSEN',   'ZIP5'

Is it possible to get an output like:

130,    'HANSEN',   'ZIP1','STREET1'
130,    'HANSEN',   'ZIP2','STREET2'
130,    'HANSEN',   'ZIP3','STREET3'
120,    'HANSEN',   'ZIP4','STREET5'
120,    'HANSEN',   'ZIP5','STREET6'

I tried with the internal connection by id, key, but I get the Cartesian product
Thank you for your help.

Helper script (if someone wants to test it):

DROP TABLE TEST1;
DROP TABLE TEST2;

CREATE TABLE TEST1 ( ID  INTEGER ,key  VARCHAR(50),VALUE1 VARCHAR(50));
CREATE TABLE TEST2 ( ID  INTEGER ,key  VARCHAR(50),VALUE2 VARCHAR(50));

INSERT INTO TEST1 VALUES (130,  'HANSEN',   'STREET1');
INSERT INTO TEST1 VALUES (130,  'HANSEN',   'STREET2');
INSERT INTO TEST1 VALUES (130,  'HANSEN',   'STREET3');
INSERT INTO TEST1 VALUES (120,  'HANSEN',   'STREET5');
INSERT INTO TEST1 VALUES (120,  'HANSEN',   'STREET6');

INSERT INTO TEST2 VALUES (130,  'HANSEN',   'ZIP1');
INSERT INTO TEST2 VALUES (130,  'HANSEN',   'ZIP2');
INSERT INTO TEST2 VALUES (130,  'HANSEN',   'ZIP3');
INSERT INTO TEST2 VALUES (120,  'HANSEN',   'ZIP4');
INSERT INTO TEST2 VALUES (120,  'HANSEN',   'ZIP5');
+3
source share
3 answers

This should do it:

with t1 as (
    select id, 
           key, 
           value1,
           row_number() over (partition by id order by key, value1) as rn
    from test1
), 
t2 as (
    select id, 
           key, 
           value2,
           row_number() over (partition by id order by key, value2) as rn
    from test2
)
select t1.id, 
       t2.key, 
       t2.value2||','||t1.value1
from t1
  join t2 on t1.id = t2.id and t1.key = t2.key and t1.rn = t2.rn
order by 1 desc, 3 asc
+1
source

This is ugly, and you may get strange results depending on the order that Oracle gives you a set of results, but you can try:

select t1.id, t1.key, t1.value1, t2.value2 
  from
     (select rownum rn, id, key, value1 from test1) t1 
          inner join
     (select rownum rn, id, key, value2 from test2) t2
          using (rn)

If you need to “merge” two tables, as you suggest, I would try adding some “merge” column to do this, rather than using this approach.

+1

. .

It looks like there is no information in your datamodel. those. somehow connect the lines from test 1 to test 2 .. the identifier / key that you have at the moment is not unique.

I think the best thing you could do would be

select *
from test1 
inner join test2 using (id,key)
order by 1 desc,2,3,4

which gives way

130 HANSEN  STREET1 ZIP1
130 HANSEN  STREET1 ZIP2
130 HANSEN  STREET1 ZIP3
130 HANSEN  STREET2 ZIP1
130 HANSEN  STREET2 ZIP2
130 HANSEN  STREET2 ZIP3
130 HANSEN  STREET3 ZIP1
130 HANSEN  STREET3 ZIP2
130 HANSEN  STREET3 ZIP3
120 HANSEN  STREET5 ZIP4
120 HANSEN  STREET5 ZIP5
120 HANSEN  STREET6 ZIP4
120 HANSEN  STREET6 ZIP5
0
source

More articles:


All Articles