CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
IMTheNachoMan
IMTheNachoMan

Reputation: 5839

join two lists with columns showing values from each table without duplicates

I have two tables. Each table has two columns. The first column of each table is the matching/mapping column. I have no idea how to explain what I am trying to do so I'll use an example.

table 1

| col1 | col2  |
|------|-------|
| a    | one   |
| a    | two   |
| b    | three |
| c    | four  |

table 2

| col1 | col2  |
|------|-------|
| a    | five  |
| b    | six   |
| b    | seven |
| d    | eight |

desired output

| col1 | table1 | table2 |
|------|--------|--------|
| a    | one    | five   |
| a    | two    |        |
| b    | three  | six    |
| b    |        | seven  |
| c    | four   |        |
| d    |        | eight  |

(the empty cells are null)

Basically I am looking for a summary table that shows all the col2 options for that col1 from each table. I hope this makes sense...

Upvotes: 0

Views: 44

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You need FULL OUTER JOIN and ROW_NUMBER

SELECT COALESCE(a.col1, b.col2),
       COALESCE(a.col2, ''),
       COALESCE(b.col, '')
FROM   (SELECT *,
               Rn = Row_number()OVER(partition BY col1 ORDER BY @@SPID)
        FROM   table1) a
       FULL JOIN (SELECT *,
                         Rn = Row_number()OVER(partition BY col1 ORDER BY @@SPID)
                  FROM   table2) b
              ON a.col1 = b.col1
                 AND a.Rn = b.Rn

Upvotes: 2

Related Questions