CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
ccampo
ccampo

Reputation: 1503

Joining a column with nulls

I have two tables (tables A and B) that look like the following:

Table A:

Col 1    Col 2
--------------
A        1
A        2
A        3
...
A        15
...

Table B:

Col 3    Col 4
--------------
A        a
A        b

I would like to write a query to join table B col 4 to table A. Table B col 3 will match table A col 1, so I would like to join on this column. The result I am looking for is:

Table C:

Col 1    Col 2    Col 4
-----------------------
A        1        a
A        2        b
A        3        null
A        4        null
....
A        15       null
....

I tried the following:

select
    tableA.col1,
    tableA.col2,
    tableB.col4
from
    tableA
inner join tableB on tableA.col1 = tableB.col3

But this yields the result:

Col 1    Col 2    Col 4
-----------------------
A        1        a
A        1        b
A        2        a
A        2        b
....
A        15       a
....

Upvotes: 0

Views: 89

Answers (5)

paparazzo
paparazzo

Reputation: 45106

Is changing the data an option?

Table A:

Col 1    Col 2
--------------
A        1
A        2
A        3
...
A        15
...
Table B:

Col 2    Col 4
--------------
1        a
2        b

SELECT tableA.col1, tableA.col2, tableB.col4
FROM tableA
LEFT JOIN tableB ON tableA.col2 = tableB.col2

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453960

WITH A AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS RN
FROM TableA
), B AS
(
SELECT *,
       ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY Col4) AS RN
FROM TableB
)
SELECT A.Col1, A.Col2, B.Col4
FROM A LEFT OUTER JOIN B ON A.Col1 = B.Col3 AND A.RN=B.RN

(SQL Fiddle)

Upvotes: 3

Germann Arlington
Germann Arlington

Reputation: 3353

I think that you are not posting the right query and/or results:
(I would have expected) 

Col 1    Col 2    Col 4
-----------------------
A        1        a
A        1        b
A        2        a
A        2        b
...

To achieve your desired result, however odd and unusual you will need to generate a sequence number for the Table B to join to the Col 2 in Table A.
The easiest way I can think of is using subquery.

Upvotes: 0

Anri
Anri

Reputation: 6265

Use left join instead of inner join.

  • inner join joins only matching rows
  • left join and right join returns full left or right table with matched rows from second table or nulls
  • full join gets all the rows from both tables.

Upvotes: 0

Ravindra Bagale
Ravindra Bagale

Reputation: 17673

use LEFT JOIN like this:

SELECT 
        tableA.col1,
        tableA.col2,
        tableB.col4
    FROM tableA
    LEFT JOIN tableB on tableA.col1 = tableB.col3

Upvotes: 0

Related Questions