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:
[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
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
Please share the query which you are trying and if possible the sample data in the tables.
Can plz tell me, how to do the same thing in Oracle.
Best Reagrds,
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……….
Thanks a lot…. It helped me a lot… 🙂
Thank you Neha
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.
Thanks for the example, I had a similar case and this helped me a lot.
How to get the two table data without using joins,sub query and union ?
how can i retrive same result if i have 200 columns an i want except CommonId by using select *…..