CODEWITHSUNDEEP
sqlsql-serverjoin
templar112
templar112

Reputation: 23

Merge two tables with DateTime primary key, but different columns

I've searched for hours with no specific answer to my problem.

I have two tables, for example:

Table A

DateAndTime             ms  us  F1      F2      F3
2012-07-07 00:00:00.000 0   0   1       1       1   
2012-07-07 01:07:40.000 750 361 1       0       1   
2012-07-07 01:07:41.000 50  11  1       0       1  
2012-07-07 01:08:35.000 850 998 1       0       0
2012-07-07 01:08:36.000 50  83  1       0       0
2012-07-07 01:08:59.000 250 26  0       0       0

Table B

DateAndTime             ms  us  F4      F5      F6
2012-07-07 00:00:00.000 0   0   1869    11      2247
2012-07-07 00:00:00.000 0   588 1869    15      2247
2012-07-07 00:05:14.000 987 359 1869    13      2251
2012-07-07 00:05:16.000 45  493 1869    11      2247
2012-07-07 00:14:15.000 985 557 1869    11      2157
2012-07-07 00:14:16.000 945 179 1869    11      2279

The result I'm looking for is to merge these two tables:

Result Table

DateAndTime             ms  us  F1      F2      F3      F4      F5      F6
2012-07-07 00:00:00.000 0   0   1       1       1       1869    11      2247
2012-07-07 00:00:00.000 0   588 NULL    NULL    NULL    1869    15      2247 
2012-07-07 00:05:14.000 987 359 NULL    NULL    NULL    1869    13      2251
2012-07-07 00:05:16.000 45  493 NULL    NULL    NULL    1869    11      2247
2012-07-07 00:14:15.000 985 557 NULL    NULL    NULL    1869    11      2157
2012-07-07 00:14:16.000 945 179 NULL    NULL    NULL    1869    11      2279
2012-07-07 01:07:40.000 750 361 1       0       1       NULL    NULL    NULL     
2012-07-07 01:07:41.000 50  11  1       0       1       NULL    NULL    NULL     
2012-07-07 01:08:35.000 850 998 1       0       0       NULL    NULL    NULL  
2012-07-07 01:08:36.000 50  83  1       0       0       NULL    NULL    NULL  
2012-07-07 01:08:59.000 250 26  0       0       0       NULL    NULL    NULL

The PRIMARY KEY in both tables is made up of (DateAndTime, ms, us).

If both tables have the same primary key then the data can be merged into the same row, otherwise there should just be new rows with NULLs where there is no data.

The column names are not always the same so I would have to build the query dynamically using C# and then execute it. I'm just trying to do as much of the work on the SQL server side before I start processing the data in C#.

Upvotes: 0

Views: 252

Answers (2)

Srini V
Srini V

Reputation: 11355

Use the below for SQL server or MY SQL

SELECT T1.DATEANDTIME,
       T1.MS,
       T1.US,
       T1.F1,
       T1.F2,
       T1.F3,
       T2.F4,
       T2.F5,
       T2.F6
FROM   T1
       LEFT OUTER JOIN
       T2
          ON ( T1.DATEANDTIME = T2.DATEANDTIME AND T1.MS = T2.MS AND T1.US = T2.US )
UNION
SELECT T2.DATEANDTIME,
       T2.MS,
       T2.US,
       T1.F1,
       T1.F2,
       T1.F3,
       T2.F4,
       T2.F5,
       T2.F6
FROM   T1
       RIGHT OUTER JOIN
       T2
          ON ( T1.DATEANDTIME = T2.DATEANDTIME AND T1.MS = T2.MS AND T1.US = T2.US );

This is for Oracle

WITH T1
     AS (SELECT '2012-07-07 00:00:00.000' AS DATEANDTIME,
                '0' AS MS,
                '0' AS US,
                '1' AS F1,
                '1' AS F2,
                '1' AS F3
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 01:07:40.000' AS DATEANDTIME,
                '750' AS MS,
                '361' AS US,
                '1' AS F1,
                '0' AS F2,
                '1' AS F3
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 01:07:41.000' AS DATEANDTIME,
                '50' AS MS,
                '11' AS US,
                '1' AS F1,
                '0' AS F2,
                '1' AS F3
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 01:08:35.000' AS DATEANDTIME,
                '850' AS MS,
                '998' AS US,
                '1' AS F1,
                '0' AS F2,
                '0' AS F3
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 01:08:36.000' AS DATEANDTIME,
                '50' AS MS,
                '83' AS US,
                '1' AS F1,
                '0' AS F2,
                '0' AS F3
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 01:08:59.000' AS DATEANDTIME,
                '250' AS MS,
                '26' AS US,
                '0' AS F1,
                '0' AS F2,
                '0' AS F3
         FROM   DUAL),
     T2
     AS (SELECT '2012-07-07 00:00:00.000' AS DATEANDTIME,
                '0' AS MS,
                '0' AS US,
                '1869' AS F4,
                '11' AS F5,
                '2247' AS F6
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 00:00:00.000' AS DATEANDTIME,
                '0' AS MS,
                '588' AS US,
                '1869' AS F4,
                '15' AS F5,
                '2247' AS F6
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 00:05:14.000' AS DATEANDTIME,
                '987' AS MS,
                '359' AS US,
                '1869' AS F4,
                '13' AS F5,
                '2251' AS F6
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 00:05:16.000' AS DATEANDTIME,
                '45' AS MS,
                '493' AS US,
                '1869' AS F4,
                '11' AS F5,
                '2247' AS F6
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 00:14:15.000' AS DATEANDTIME,
                '985' AS MS,
                '557' AS US,
                '1869' AS F4,
                '11' AS F5,
                '2157' AS F6
         FROM   DUAL
         UNION ALL
         SELECT '2012-07-07 00:14:16.000' AS DATEANDTIME,
                '945' AS MS,
                '179' AS US,
                '1869' AS F4,
                '11' AS F5,
                '2279' AS F6
         FROM   DUAL)
SELECT T1.DATEANDTIME,
       T1.MS,
       T1.US,
       T1.F1,
       T1.F2,
       T1.F3,
       T2.F4,
       T2.F5,
       T2.F6
FROM   T1
       LEFT OUTER JOIN
       T2
          ON ( T1.DATEANDTIME = T2.DATEANDTIME AND T1.MS = T2.MS AND T1.US = T2.US )
UNION
SELECT T2.DATEANDTIME,
       T2.MS,
       T2.US,
       T1.F1,
       T1.F2,
       T1.F3,
       T2.F4,
       T2.F5,
       T2.F6
FROM   T1
       RIGHT OUTER JOIN
       T2
          ON ( T1.DATEANDTIME = T2.DATEANDTIME AND T1.MS = T2.MS AND T1.US = T2.US );

Which produce

2012-07-07 00:00:00.000 0   0   1   1   1   1869    11  2247
2012-07-07 00:00:00.000 0   588             1869    15  2247
2012-07-07 00:05:14.000 987 359             1869    13  2251
2012-07-07 00:05:16.000 45  493             1869    11  2247
2012-07-07 00:14:15.000 985 557             1869    11  2157
2012-07-07 00:14:16.000 945 179             1869    11  2279
2012-07-07 01:07:40.000 750 361 1   0   1           
2012-07-07 01:07:41.000 50  11  1   0   1           
2012-07-07 01:08:35.000 850 998 1   0   0           
2012-07-07 01:08:36.000 50  83  1   0   0           
2012-07-07 01:08:59.000 250 26  0   0   0

Note: UNION removes duplicates. I don't use full outer join since it is not supported across all DB's

Upvotes: 0

RobertKing
RobertKing

Reputation: 1921

try below query..

SELECT A.DATEANDTIME,A.MS,A.US,A.F1,A.F2,A.F3,B.F4,B.F5,B.F6 FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.DATEANDTIME = B.DATEANDTIME
UNION
SELECT B.DATEANDTIME,B.MS,B.US,A.F1,A.F2,A.F3,B.F4,B.F5,B.F6 FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.DATEANDTIME = B.DATEANDTIME

Upvotes: 1

Related Questions