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

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.

From stackoverflow

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

  KM : this will only work for this sample data

  dr : it provided a way to answer the problem that was originally posted.

• 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
  

  KM : when run on the given sample data, it returns 4 rows. You need to remove the "Partition by foo_id" and "partition by bar_id" to make it work properly. I use the same approach sing row_number(), see my solution.

• 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.

  KM : this will only work for the simple sample data

  codeulike : yeah, thought it might. The alternative second part of my answer is to add a new column, but I know thats not much better either : )

• 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
  

  : Found an article that goes over this kind of approach - http://www.mssqltips.com/tip.asp?tip=1257

• 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