One to one record mapping without any common column between two tables in SQL server

Question

I'm trying to join two tables based on one to one mapping without any common column.

For example, I have 3 records in the first table and 2 records in the second table.

The mapping should be as follows:

As there is no third record in the second table, nulls should be displayed.

In order to implement this, I've tried the following code:

declare @FirstTable table (StudentId int, SubjectId int)
declare @SecondTable table (MarksId int, RankId int, LastRank int)

insert into @FirstTable values (1, 1)
insert into @FirstTable values (1, 2)
insert into @FirstTable values (1, 3)

insert into @SecondTable values (1, 4, 10)
insert into @SecondTable values (1, 5, 11)

;with XmlTable (RowNumber, StudentId, SubjectId) as
(
    select row_number() over(order by StudentId) as RowNumber, * from @FirstTable
)
,TechnicalIdsTable (RowNumber, MarksId, RankId, LastRank) as
(
    select row_number() over(order by MarksId) as RowNumber, * from @SecondTable
)
select x.StudentId, x.SubjectId, t.MarksId, t.RankId, t.LastRank from XmlTable x 
left join TechnicalIdsTable t
on x.RowNumber = t.RowNumber

Based on the above code, I am able to achieve the output. Let me know whether this is a proper approach or not.

Here's a new example:

In this case, there are three students enrolled for sports, you can check the order in which they enrolled. But there are only two vacant sports available and the order in which they have to be selected.

Now using my query, which will be

declare @Students table (StudentId int, StudentName varchar(50), EnrolledOn datetime)
declare @Sports table (SportId int, SportName varchar(50))

insert into @Students values (1, 'John', '2013-05-25 10:00:00')
insert into @Students values (2, 'Jerry', '2013-05-25 14:30:00')
insert into @Students values (3, 'Jane', '2013-05-26 10:30:00')

insert into @Sports values (1, 'Golf')
insert into @Sports values (2, 'Foot ball')

;with Students (RowNumber, StudentId, StudentName, EnrolledOn) as
(
    select row_number() over(order by EnrolledOn) as RowNumber, * from @Students
)
,Sports (RowNumber, SportId, SportName) as
(
    select row_number() over(order by SportId) as RowNumber, * from @Sports
)
select s1.StudentId, s1.StudentName, s1.EnrolledOn, s2.SportId, s2.SportName from Students s1
left join Sports s2
on s1.RowNumber = s2.RowNumber

I'm getting the expected output.

My goal is to assign the 1^st sport to the student who enrolled in the first and second sport to the second student, and for the third student there will be no sport.

Will this be proper now?

Answer 1

Even if your solution is (almost) correct, the actual problem is in a wrong formulation of the problem.

The main issue in your question is the assumption of a certain order of records within a table. In reality SQL Server cannot guarantee original order of records when you do a select statement without order by clause, even if you have a clustered index defined.

In order to properly map 2 tables you must have a criteria for matching records. You provided a solution that won't generate consistent results. Imagine that you need to add another student/subject (StudentId = 2, SubjectId = 1) and a mark for that. If you add one record to Student/Subject table and one record to Marks table your data will be screwed up since Marks record will be matched against 3rd record (StudentId = 1, SubjectId = 3).

In order to fix the issue you must add a field or fields that allow unique reference of records, that is you must add both StudentId and SubjectId to Marks table. Alternatively you can create a surrogate key in the Student/Subject table and use it as a reference in Marks table.

UPDATE. The main difference between original question and example in Edit section is that records can be ordered by unique key (primary key) in latter case, while in original question order by StudentId didn't guarantee specific order and produced inconsistent results.

Your updated example is using mapping properly, according to (updated) requirements, and will produce consistent results as long as SportId and StudentId are unique in corresponding tables