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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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