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

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 1st 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?

• Even if you can get a query to line up the rows in some manner (using a calculated row ID), it may not be stable or survive backup/restore. Why can't you add the PK from one of the tables to the other? May 27 '13 at 13:02
• actually one is a temporary table (with data from UI) and second one is a table variable... I have to combine these two tables on one-to-one mapping and insert these records into a table. so can't have a primary / foreign key May 27 '13 at 13:42
• In your example above, how can Jane enroll in a sport? Are the student records inserted in the order in which they enroll in the sport from the sports table? Can't you add StudentId to the sports table without making it a proper FK. May 27 '13 at 15:08
• In the example, Jane cannot enroll into any sport as there are no vacant sports. Yes the students records are inserted in the order in which they are enrolled. As I mentioned previously, these are not database tables. May 29 '13 at 6:08

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

• That's a good explanation.. I did not know about this. I think I confused you with the marksid and rankid columns. Please check my edit for more info. May 27 '13 at 11:44
• I case if I want to get uniqueness for my first example, for first table, can I order by "StudentId, SubjectId" and for second table, by "MarksId, RankId". I guess this should solve the problem for first example. am I correct ? May 27 '13 at 12:39
• @Harsha Well, if that's what you want... What happens if MarksId=1 (whatever it means) gets a new rank with RankId=1? In your example it would shift all the other records and will be assigned to the first record in student/subjects table May 27 '13 at 13:46
• yes.. in that case, the query fails. Its better I modify my tables to add unique columns. Thank you for your answer. May 29 '13 at 6:13