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:

enter image description here

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:

enter image description here

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?

  • \$\begingroup\$ 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? \$\endgroup\$ Commented May 27, 2013 at 13:02
  • \$\begingroup\$ 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 \$\endgroup\$
    – Harsha
    Commented May 27, 2013 at 13:42
  • \$\begingroup\$ 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. \$\endgroup\$ Commented May 27, 2013 at 15:08
  • \$\begingroup\$ 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. \$\endgroup\$
    – Harsha
    Commented May 29, 2013 at 6:08

1 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

  • \$\begingroup\$ 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. \$\endgroup\$
    – Harsha
    Commented May 27, 2013 at 11:44
  • \$\begingroup\$ 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 ? \$\endgroup\$
    – Harsha
    Commented May 27, 2013 at 12:39
  • \$\begingroup\$ @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 \$\endgroup\$
    – almaz
    Commented May 27, 2013 at 13:46
  • \$\begingroup\$ yes.. in that case, the query fails. Its better I modify my tables to add unique columns. Thank you for your answer. \$\endgroup\$
    – Harsha
    Commented May 29, 2013 at 6:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.