CODEWITHSUNDEEP
sqlsql-serverjoin
Proxy404
Proxy404

Reputation: 189

Join two tables with mismatching number of rows

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

Upvotes: 0

Views: 259

Answers (2)

Taryn
Taryn

Reputation: 247860

If you are using a database that supports FULL OUTER JOIN syntax then you can use:

select 
  coalesce(ot.record_id, nt.record_id) record_id,
  ot.name OT_Name,
  nt.name NT_name,
  coalesce(ot.sequence_num, nt.sequence_num) Sequence_num
from old_table ot
full outer join new_table nt
  on ot.record_id = nt.record_id
  and ot.sequence_num =  nt.sequence_num

See SQL Fiddle with Demo

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The following will work in any database. It doesn't rely on full outer join:

select record_id,
       MAX(case when which = 'ot' then name end) as ot_name,
       MAX(case when which = 'nt' then name end) as nt_name,
       sequence_num
from ((select record_id, name, sequence_num, 'ot' as which
       from ot
      ) union all
      (select record_id, name, sequence_num, 'nt' as which
       from nt
      )
     ) t
group by record_id, sequence_num

Upvotes: 2

Related Questions