T/SQL Puzzle - How to join to create one-to-one relationship for two unrelated tables?

Question

Let's assume that I have two tables... Foo and Bar. They contain the following data.

Table Foo:
Foo_Id
------
100
101

Table Bar:
Bar_Id
------
200
201

As you can see, each table has two records. I'd like to join these tables together in a way where they return two records; the ultimate goal is to create a one to one relationship for these records even though at this state they do not have that relationship. The results of this data would go into table Foo_Bar to store this new relationship.

Ideally, the output would look similar to the following.

Foo_Id  Bar_Id
------  ------
100     200
101     201

This code will be used in a T/SQL stored procedure. I could write this easily with a while loop, but I would prefer not to use a while loop because the real world application will have a lot more data than four records and will by called by multiple users many times per day.

Thanks in advance!

EDIT:

It's more or less an inventory problem... I've got 100 slices of pizza and 100 people who say they want a slice of pizza. The Foo_Bar table is basically a way to assign one slice of pizza per person. The table exists and this solution will load the data for the table.

Answer 1

select foo_id, bar_id from foo inner join bar on foo_id = (bar_id -100)

Answer 2

select from each table as a nested select statement with a rank function, then join on the rank.

select foo_id, bar_id
from (select foo_id, rank() over (partition by foo_id order by foo_id) as [Rank] from foo) f
left join (select bar_id, rank() over (partition by bar_id order by bar_id) as [Rank] from bar) b
on f.Rank = b.Rank

Answer 3

From the example you've given, you might be able to exploit the fact that bar_id = 100 + foo_id

select foo.foo_id, bar.bar_id
from foo inner join bar on foo.foo_id +100 = bar.bar_id

.. but maybe that is just a simplification in your example?

If there is no relatinship between the IDs, then its not really relational data anymore, and there's no easy way to join the rows in a Relational Database.

In which case you would have to add a new column to one of the tables and add a foreign key so that the data does become relational.

Answer 4

Assigning to inventory is typically going to be a one row at a time operation. Using sets for this is great but it is kind of a "batch processing" paradigm. I would envision various people get into line and acquire pizza from the inventory table. Mssql has some useful hints for this get-next-available-item pattern - look at the READPAST hint. To acquire a pizza you might do something like

UPDATE mytable WITH (READPAST) SET AcquiringUserID = @userId where AcquiringUserId is null

Answer 5

try this:

declare @Foo table (Foo_Id int)
INSERT INTO @Foo VALUES (100)
INSERT INTO @Foo VALUES (101)
declare @Bar table (Bar_Id int)
INSERT INTO @Bar VALUES (200)
INSERT INTO @Bar VALUES (201)


SELECT
    dt_f.Foo_Id
        ,dt_f.RowNumber
        ,dt_b.Bar_Id
    FROM (SELECT
              Foo_Id, ROW_NUMBER() OVER(ORDER BY Foo_Id) AS RowNumber
              FROM @Foo
          ) dt_f
        INNER JOIN (SELECT
                        Bar_Id, ROW_NUMBER() OVER(ORDER BY Bar_Id) AS RowNumber
                        FROM @Bar
                   ) dt_b ON dt_f.RowNumber=dt_b.RowNumber