This tutorial explains how to apply self join in SQL query.
Example 1 : Find out Manager
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager name against each employee ID.
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager of manager's name against each employee ID.
Example 3 : Find out Grand Son
Example 1 : Find out Manager
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager name against each employee ID.
Proc SQL : Self Join |
data example2;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 .
William 222 456
Daniel 777 222
Cook 383 222
;
run;
SQL Query : Self Join
proc sql;Example 2 : Find out Manager of Manager
create table want as
select a.*, b.Name as Manager
from example2 as a left join example2 as b
on a.managerid = b.id;
quit;
Suppose you have data for employees. It comprises of employees' name, ID and manager ID. You need to find out manager of manager's name against each employee ID.
Self Join SQL Query |
data example22;
input Name $ ID ManagerID;
cards;
Smith 123 456
Robert 456 777
William 222 123
Daniel 777 .
Cook 383 456
;
run;
proc sql;
create table want as
select a.Name, a.ID, a.managerid, b.ManagerID as ManagerofManagerID
from example22 a left join example22 b
on a.managerid = b.id;
quit;
proc sql;
create table want2 as
select a.*, b.Name as ManagerofManagerName
from want as a left join want as b
on a.ManagerofManagerID = b.id;
quit;
Example 3 : Find out Grand Son
data example22;
input Parent $ Child $;
cards;
A1 B1
A2 B3
B1 C1
C1 D2
B3 C3
;
run;
proc sql;
create table want as
select a.Parent as GrandParent, b.Child as GrandChild
from example22 a left join example22 b
on a.child = b.parent;
quit;
Awesome explanation...
ReplyDeleteUnderstood it clearly... Thanks a lot for posting..
Nice work.
ReplyDeleteCan you elaborate joins using Proc Format and SAS Hashing