Joining Two Tables without any Common Column between them – 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

11 thoughts on “Joining Two Tables without any Common Column between them – Sql Server

  1. Please can anyone in this team help me to ans the below:

    i have joined more than 2 tables what could be the reason if i am not getting all the data

    thanks in advance

    1. Please share the query which you are trying and if possible the sample data in the tables.

  2. Emptbl transaction tbl
    ———————————————————————

    Empno details trno transact details

    123456 aaa 1234561 axbxcx
    1234567 bbb 1234562 bxbybzx
    12345678 ccc 1234563 aybycy
    123456789 ddd 1234564 bbb

    i need to select “empno” and “transact details” from the above tables.
    but there is no common column. in the second table just 6 common letters are there. how can i get the data?
    pls help me……….

  3. I have two tables without relationship, but i date cloumn so i want to fetch top 50 based on the date.means data from 2 tables based on date desc should come as single select statement.

Leave a Reply

Your email address will not be published. Required fields are marked *