I have two tables:

Old\_table (OT)

```
 Record_id |  Name  |  Sequence_num
------------------------------------
    78     | Austin |  0
    78     | Mike   |  1
    78     | Joe    |  2

```

and

New\_table (NT)

```
 Record_id |  Name  |  Sequence_num
------------------------------------
    78     | Mike   |  0
    78     | Joe    |  1
    78     | Austin |  2
    78     | Fred   |  3
    78     | Ben    |  4

```

What I'm looking for is the output table to look like this:

```
 Record_id |  OT_Name  |  NT_Name  |  Sequence_num
---------------------------------------------------
    78     |  Austin   |  Mike     |  0
    78     |  Mike     |  Joe      |  1
    78     |  Joe      |  Austin   |  2
    78     |  NULL     |  Fred     |  3
    78     |  NULL     |  Ben      |  4

```

The issue is that I don't know for certain how many rows will be in each table. OT could have 10 rows and NT could have 3, or NT could have more than OT, or they could have the same number. Any rows that don't have a matching Sequence\_num in the opposite table will need to have a NULL value in the appropriate column. Short of creating a function for every table with this situation, how could a select statement accomplish this? I can not for the life of me come up with a solution.

Edit:

Using MS Sql Sever 2008

Management Studio 10.0.1600.22