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?