enrique_aeo
asked on
sql server 20212 CTE or temporary tables
Hi experts
i have this query
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees
UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
FROM EmpOrg_CTE AS parent
JOIN HR.Employees AS child
ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;
there is another way to do this without using CTE?
i have this query
WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees
UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
FROM EmpOrg_CTE AS parent
JOIN HR.Employees AS child
ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;
there is another way to do this without using CTE?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you mean that it is very difficult to do with temporary tables?
ASKER
I ATTached the data
hrEMPLOYEES.txt
hrEMPLOYEES.txt
here you go
CREATE table #tmpEmp(empId int, mgrId int, firstname nchar(10), lastname nchar(10))
insert into #tmpEmp
SELECT empid, mgrid, firstName, lastName
FROM Emp
WHERE empid = 5
union all
select B.empId, B.mgrId, B.firstName, B.lastName
from Emp A, Emp B
where A.empId = 5 -- You can change this
And (B.MgrId = A.empid or B.MgrId = null)
select * from #tmpEmp
not at all difficult to do with temp table
CREATE table #tmpEmp(empId int, mgrId int, firstname nchar(10), lastname nchar(10))
insert into #tmpEmp
SELECT empid, mgrid, firstName, lastName
FROM Emp
WHERE empid = 5
union all
select B.empId, B.mgrId, B.firstName, B.lastName
from Emp A, Emp B
where A.empId = 5 -- You can change this
And (B.MgrId = A.empid or B.MgrId = null)
select * from #tmpEmp
not at all difficult to do with temp table
ASKER
I ATTached the data
data.xlsx
data.xlsx
ASKER
the query is not working
CREATE table #tmpEmp(empId int, mgrId int, firstname nchar(10), lastname nchar(10))
insert into #tmpEmp
SELECT empid, mgrid, firstName, lastName
FROM
.[Employees]
WHERE empid = 5
union all
select B.empId, B.mgrId, B.firstName, B.lastName
from #tmpEmp A, #tmpEmp B
where A.empId = 5 -- You can change this
And (B.MgrId = A.empid or B.MgrId = null)
select * from #tmpEmp
CREATE table #tmpEmp(empId int, mgrId int, firstname nchar(10), lastname nchar(10))
insert into #tmpEmp
SELECT empid, mgrid, firstName, lastName
FROM
.[Employees]
WHERE empid = 5
union all
select B.empId, B.mgrId, B.firstName, B.lastName
from #tmpEmp A, #tmpEmp B
where A.empId = 5 -- You can change this
And (B.MgrId = A.empid or B.MgrId = null)
select * from #tmpEmp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER