Can this be done with something like a JOIN?

My question is: I have two tables: table A has two columns (KeyA and Match) and table B has two columns (KeyB and Match). I want to compare with the "Match" column.

If table A has 3 rows with a particular "Match", and table B has 2 rows, a JOIN will return me all the combinations (6 in this case). What I want it to do is match up as many as it can, and then NULL out the others.

So, it would match the first "KeyA" with the first "KeyB", the second "KeyA" with the second "KeyB", and then match up the third "KeyA" with NULL, since table B only has two rows for this "Match". The order is actually irrelevant, just as long as 2 rows match up, and then one value from table A returns with a NULL for the table B value. This is not like an INNER or an OUTER JOIN.

I hope this makes sense, it was difficult to express clearly, and was hard to find keywords to search on.

EDIT: An INNER/OUTER join would match all the table A values with all of the table B values it could. Once a B value is "used up" I do not want it to match it with any other A values.

Example: Table A开发者_如何学编程 (KeyA, Match)

(1, "a")

(2, "a")

(3, "a")

Table B (KeyB, Match)

(11, "a")

(12, "a")

Desired output (KeyA, Match, KeyB): (1, "a", 11)

(2, "a", 12)

(3, "a", NULL)

---

You can use partition by to number the rows for each value of match. Then you can use full outer join to fill up rows per Match. For example:

declare @A table (KeyA int, match int)
insert @A values (1,1), (2,1), (3,1), (4,2), (5,2), (6,2)
declare @B table (KeyB int, match int)
insert @B values (1,1), (2,1), (3,2)

select  *
from    (
        select  row_number() over (partition by match order by KeyA) as rn
        ,       *
        from    @A
        ) as A
full outer join
        (
        select  row_number() over (partition by match order by KeyB) as rn
        ,       *
        from    @B
        ) as B
on      A.match = B.match
        and A.rn = B.rn
        

Working code at SE Data.

---

declare @TableA table(ID int, Name varchar(10))
declare @TableB table(ID int, Name varchar(10))

insert into @TableA values(1, 'a'), (1, 'b'), (1, 'c')
insert into @TableB values (1, 'A'), (1, 'B')

insert into @TableA values(2, 'a'), (2, 'b')
insert into @TableB values (2, 'A'), (2, 'B'), (2, 'C')

;with A as
(
  select *,
         row_number() over(partition by ID order by Name) as rn
  from @TableA
),
B as
(
  select *,
         row_number() over(partition by ID order by Name) as rn
  from @TableB
)
select A.ID as AID,
       A.Name as AName,
       B.ID as BID,
       B.Name as BName
from A  
  full outer join B  
    on A.ID = B.ID and
       A.rn = B.rn

Result:

AID         AName      BID         BName
----------- ---------- ----------- ----------
1           a          1           A
1           b          1           B
1           c          NULL        NULL
2           a          2           A
2           b          2           B
NULL        NULL       2           C

---

SELECT
    ar.Match
    COALESCE(ar.RowN, br.RowN) AS RowNumber
    ar.KeyA
    br.KeyB
FROM
    ( SELECT KeyA
           , Match
           , ROW_NUMBER() OVER(PARTITION BY Match) AS RowN
    ) AS ar
  LEFT JOIN                      --- or FULL JOIN
    ( SELECT KeyB
           , Match
           , ROW_NUMBER() OVER(PARTITION BY Match) AS RowN
    ) AS br
    ON  br.Match = ar.Match
    AND br.RowN = ar.RowN

---

I think what you are looking for is called a Cross Join, or Cartesian Product.

http://www.sqlguides.com/sql_cross_join.php

edit - Hm now actually I'm not so sure.

---

As far as I can understand, what you are looking for is a FULL JOIN, or also called CROSS JOIN.

Check out this link. It has good explanation of all types of joins:

http://www.w3schools.com/sql/sql_join.asp