CODEWITHSUNDEEP
sqloracle-databasemerge
user2217874
user2217874

Reputation: 141

Merging two tables into one in Oracle SQL

I have two tables, each with many attributes. There are attributes that I want from each, and I am trying to write a query to get those attributes, and put them into three columns.

Table A

LOCATION_NUMBER (varchar2(20Byte)), 
BEGIN (NUMBER (3,0)), 
BEGIN_DEC (NUMBER (4,3)), 
END (NUMBER (3,0)), 
END_DEC (NUMBER (4,3))

Table B

LOCNUM (NUMBER (15,0)), 
START (VARCHAR2(6 BYTE)), 
START_POS (NUMBER (5,3)), 
FINISH (VARCHAR2 (6 BYTE)), 
FINISH_POS (NUMBER (5,3)).

What I want from the query is one table, with three attributes and then order these tables by the location first, and start second.

Table C, 

LOCATION, 
START, 
END

Here's an example:

A:

LOCATION_NUMBER    BEGIN    BEGIN_DEC    END    END_DEC
      I 26          8          0.63       13      0.99
      I 32          144        0          151     0.82
      I 35          22         0.29       55      0.09
      I 35          128        0.1        67      0.9
      I 35          0          0          22      0.28

B:

LOC_NUM            START    START_POS    FINISH  FINSIH_POS
      227           98         0.56       100     0
      35            149        0.25       151     0.55
      96            156        0.23       156     1.26
      11            263        2.56       263     4.52

RESULT SET:

LOCATION       START        END
    26          8.63         13.99
    32          144          151.82
    35          22.29        55.09
    35          128.1        67.9
    35          0            22.28   
    227         98.56        100
    35          149.25       151.55
    96          156.23       157.26
    11          265.56       267.52

I have some queries that I tried I am able to get the desired results from each table, I just need to know how to combine them into one to make things easier for what I want the table for, again, each of the tables have 10 - 20 more attributes that I don't want to worry about.

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
(to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC))) AS START,
(to_number(trim(A.END)) + to_number(trim(A.END_DEC))) AS END
FROM A
ORDER BY to_number(trim(regexp_substr(A.LOCATION_NUMBER,'[^ ]+', 1, 2)));

SELECT B.LOC_NUM AS LOCATION,
(to_number(trim(B.START)) + to_number(trim(B.START_POS))) AS START,
(to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS))) AS END
FROM B
ORDER BY B.LOCATION, START;

Upvotes: 4

Views: 6010

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming your queries work, you just want union all:

Select to_number(trim(regexp_substr(A.LOCATION_NUMBER ,'[^ ]+', 1, 2))) AS LOCATION,
       to_number(trim(A.BEGIN)) + to_number(trim(A.BEGIN_DEC)) AS START,
       to_number(trim(A.END)) + to_number(trim(A.END_DEC)) AS END
FROM A
UNION ALL
SELECT B.LOC_NUM AS LOCATION,
       to_number(trim(B.START)) + to_number(trim(B.START_POS)) AS START,
       to_number(trim(B.FINISH)) + to_number(trim(B.FINISH_POS)) AS END
FROM B
ORDER BY B.LOCATION, START;

Upvotes: 5

Related Questions