CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Pipster1981
Pipster1981

Reputation: 35

Alternative to using temp tables in a view

Afternoon, apologies for the newbie question but I've written the following SQL code using temp tables and I want to use it as a view, I'm sure that it can be written without using temp tables but if someone could help me and point me in the right direction as to what i need to do, that would be greatly appreciated. SQL Server 2008 R2

select R.Code, R.Name, R.[Department Code], R.[Line Role]
into #first
from [Employment Role] R

select R.Code, R.Name, R.[Department Code], R.[Line Role]
into #second
from [Employment Role] R

select R.Code, R.Name, R.[Department Code], R.[Line Role]
into #senior
from [Employment Role] R

select emp.[First Name], emp.[Last Name], f.Name AS [Employee Job Title], f.[Department Code] as [Employee Department], 
       s.Name as [Manager Job Title], s.[Department Code] as [Manager Department], snr.Name as [Senior Manager Job Title], 
       snr.[Department Code] as [Senior Manager Department]
from #first f
join #second s on f.[Line Role] = s.Code
join #senior snr on s.[Line Role] = snr.Code
join [Employee] Emp on Emp.[Role Name] = f.Name

drop table #first
drop table #second
drop table #senior

Upvotes: 0

Views: 1044

Answers (3)

Grax32
Grax32

Reputation: 4059

This looks like it would work with just aliases, without using CTE.

select emp.[First Name], emp.[Last Name], f.Name AS [Employee Job Title],
 f.[Department Code] as [Employee Department],
 s.Name as [Manager Job Title],
 s.[Department Code] as   [Manager Department],
 snr.Name as [Senior Manager Job Title],
 snr.[Department Code] as [Senior Manager Department]
from [Employment Role] f
join [Employment Role] s on f.[Line Role] = s.Code
join [Employment Role] snr on s.[Line Role] = snr.Code
join [Employee] Emp on Emp.[Role Name] = f.Name

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

It looks like your temp tables all have the same data, so you can just use one cte and join it to itself:

WITH    cte
          AS ( SELECT   R.Code ,
                        R.Name ,
                        R.[Department Code] ,
                        R.[Line Role]
               FROM     [Employment Role] R
             )
    SELECT  emp.[First Name] ,
            emp.[Last Name] ,
            f.Name AS [Employee Job Title] ,
            f.[Department          Code] AS [Employee Department] ,
            s.Name AS [Manager Job Title] ,
            s.[Department Code] AS [Manager Department] ,
            snr.Name AS [Senior Manager Job Title] ,
            snr.[Department Code] AS [Senior Manager Department]
    FROM    cte f
            INNER JOIN cte s ON f.[Line Role] = s.Code
            INNER JOIN cte snr ON s.[Line Role] = snr.Code
            INNER JOIN [Employee] Emp ON Emp.[Role Name] = f.Name

Upvotes: 0

divan
divan

Reputation: 29

WITH first_CTE (Code, Name, [Department Code], [Line Role])
AS
(
    select R.Code, R.Name, R.[Department Code], R.[Line Role]
      from [Employment Role] R
), second_CTE (Code, Name, [Department Code], [Line Role])
AS
(
    select R.Code, R.Name, R.[Department Code], R.[Line Role]
      from [Employment Role] R
)

select *
  from first_CTE join second_CTE ...

Upvotes: 0

Related Questions