I'm creating a view of individuals in a department. That is easy.
Select p.PREmp, p.PRDept, 0 As [Admin] From Payroll p
This lists all people, their departments and 0 as Admin.
I have a second table with two entries
Select h.PREmp, * As [PRDept], 1 As [Admin] From HR h Where h.PositionCode = 'HR' Or h.PositionCode = 'PR'
These two individuals are Admins for every PRDept in the first table. How do as add these to rows to each PRDept in a view. I can do it elsewhere by creating two temp tables and Union All but can I do this in a view?
Thank you,
February 17, 2020 at 10:24 pm
You need to do it all in a single query (which can include UNION ALL), but not using temp tables.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Select p.PREmp, p.PRDept, 0 As [Admin]
From Payroll p
UNION ALL
Select h.PREmp, p.PRDept, 1 As [Admin]
From HR h
cross join (
select distinct PRDept
from payroll
) AS p
Where h.PositionCode = 'HR' Or h.PositionCode = 'PR'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 19, 2020 at 1:37 pm
Awesome, Cross Join with Union All worked perfect.
Thank you,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply