Tag Archives: One-to-One record mapping between two tables records which don’t have any common column between them for joining

Joining Two Tables without any Common Column between them – Sql Server

Scripts, Sql Server, , ,

A friend of mine was needed a script where he wanted a One-to-One record mapping between two tables records which don’t have any common column between them for joining. That is he wanted to match the first record in the first table to the first record in the second table, second record in the first table to the second record in the second table and so. on. Let me represent his requirement pictorially:

TowTableRecordsOneToOneMappingWithoutAnyCommonColumn

[ALSO READ] Joins In Sql Server

We can achieve this by a script like below:

CREATE DATABASE SqlHintsDemo
GO
USE SqlHintsDemo
GO
--- Create Mentor Table and populate Mentor's data
CREATE TABLE Mentor (MentorId int, MentorName Varchar(40))
 
INSERT INTO Mentor (MentorId, MentorName) VALUES (100, 'Prabhu')
INSERT INTO Mentor (MentorId, MentorName) VALUES (200, 'Basavaraj')
INSERT INTO Mentor (MentorId, MentorName) VALUES (300, 'Sharan')
INSERT INTO Mentor (MentorId, MentorName) VALUES (400, 'Keerthi')
 
--- Create Mentee Table and populate Mentor's data
CREATE TABLE Mentee (MenteeId int, MenteeName Varchar(40))
 
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (1, 'Anna')
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (2, 'ShreeGanesh')
INSERT INTO Mentee (MenteeId, MenteeName) VALUES (3, 'Shashank')
 
--- USE CTE to get the expected results
;WITH MentorTable (CommonId, MentorId, MentorName) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY MentorId) AS CommonId,MentorId, MentorName 
    FROM Mentor
)
,MenteeTable (CommonId, MenteeId, MenteeName) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY MenteeId) AS CommonId,MenteeId, MenteeName 
    FROM Mentee
)
SELECT MR.MentorId,MR.MentorName,ME.MenteeId,ME.MenteeName
FROM MentorTable MR
    LEFT OUTER JOIN MenteeTable ME
        ON MR.CommonId = ME.CommonId

[ALSO READ] Joins In Sql Server