August 30, 2011 at 8:22 am
I have the following tables:
Employee
Empid EmpName EmpDoB
employeeDepend
empid DepName DepDoB
DepartmentEmployees
DeptId EmpId
Department
DeptId DeptName
I would like the following info returned
EmpId, EmpName, DeptName, NumberofDependents,
Where the number of dependents for the employee > 2
can anyone please help me formulate the query?
thanks,
jon
August 30, 2011 at 8:31 am
Certainly!
First though, please read this[/url] for information on how best to post a question that will get your question answered quickly.
Thanks
August 30, 2011 at 8:43 am
Sorry about that - thanks for the pointer and I will create the base SQL for this and post it.
August 30, 2011 at 9:10 am
Some base SQL to set up the tables
IF OBJECT_ID('TempDB..#emp','U') IS NOT NULL
DROP TABLE #emp
CREATE TABLE #emp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmdId int,
EmpLName varchar(100),
EmpFName varchar(100)
)
IF OBJECT_ID('TempDB..#empDepend','U') IS NOT NULL
DROP TABLE #empDepend
CREATE TABLE #empDepend
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmdId int,
DepName varchar(100)
)
IF OBJECT_ID('TempDB..#Dept','U') IS NOT NULL
DROP TABLE #Dept
CREATE TABLE #Dept
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DeptId int,
DeptName varchar(100)
)
IF OBJECT_ID('TempDB..#DeptEmp','U') IS NOT NULL
DROP TABLE #DeptEmp
CREATE TABLE #DeptEmp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DeptId int,
EmdId int,
)
SET IDENTITY_INSERT #emp ON
SET IDENTITY_INSERT #empDepend ON
SET IDENTITY_INSERT #Dept ON
SET IDENTITY_INSERT #DeptEmp ON
INSERT INTO #emp ( EmpLName, EmpFName)
select 'smith', 'jon' UNION ALL
select 'doe', 'jane' UNION ALL
select 'cooper', 'dd'
GO
INSERT INTO #empDepend ( EmdId, DepName)
select 1, 'Litte jonny smith' UNION ALL
select 2, 'little janny doe' UNION ALL
select 2, 'baby jimmy doe' UNION ALL
select 3, 'ee coopper' UNION ALL
select 3, 'ff coopper' UNION ALL
select 3, 'gg coopper'
GO
INSERT INTO #Dept (DeptId, DeptName)
select 1, 'Marketing' UNION ALL
select 2, 'Support' UNION ALL
select 3, 'Engineering'
GO
INSERT INTO #DeptEmp (DeptId, EmdId)
select 1, 1 UNION ALL
select 2, 2 UNION ALL
select 3, 3
GO
SET IDENTITY_INSERT #emp OFF
SET IDENTITY_INSERT #empDepend OFF
SET IDENTITY_INSERT #Dept OFF
SET IDENTITY_INSERT #DeptEmp OFF
August 30, 2011 at 9:37 am
Thanks for the sample data 🙂
Not sure what version of SQL Server you're using.
Try this (2005+ only)
SELECT EmdId, EmpName, DeptName, MAX(rn) AS NumberofDependents
FROM (SELECT e.EmdId, e.EmpFName + ' ' + e.EmpLName AS EmpName,
d.DeptName, ROW_NUMBER() OVER(partition by e.EmdId ORDER BY (SELECT NULL)) AS rn
FROM #emp e
INNER JOIN #DeptEmp de ON e.EmdId = de.EmdId
INNER JOIN #Dept d ON d.DeptId = de.DeptId
INNER JOIN #empDepend ed ON ed.EmdId = e.EmdId) sq
--This is greater than 2 dependents, if you want greater than or equal to then
--change to WHERE rn >= 2
WHERE rn > 2
GROUP BY EmdId, EmpName, DeptName
Or this: -
SELECT EmdId, EmpName, DeptName, COUNT(EmdId) AS NumberofDependents
FROM (SELECT e.EmdId, e.EmpFName + ' ' + e.EmpLName AS EmpName,
d.DeptName
FROM #emp e
INNER JOIN #DeptEmp de ON e.EmdId = de.EmdId
INNER JOIN #Dept d ON d.DeptId = de.DeptId
INNER JOIN #empDepend ed ON ed.EmdId = e.EmdId) sq
GROUP BY EmdId, EmpName, DeptName
HAVING COUNT(EmdId) > 2
Oh, btw, I had to change your INSERT statements because you had no "EmdID" in the #emp table. I figured the ID column was supposed to be the EmdID column. So I ended up running it against this: -
IF OBJECT_ID('TempDB..#emp','U') IS NOT NULL
DROP TABLE #emp
CREATE TABLE #emp
(
EmdId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmpLName varchar(100),
EmpFName varchar(100)
)
IF OBJECT_ID('TempDB..#empDepend','U') IS NOT NULL
DROP TABLE #empDepend
CREATE TABLE #empDepend
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
EmdId int,
DepName varchar(100)
)
IF OBJECT_ID('TempDB..#Dept','U') IS NOT NULL
DROP TABLE #Dept
CREATE TABLE #Dept
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DeptId int,
DeptName varchar(100)
)
IF OBJECT_ID('TempDB..#DeptEmp','U') IS NOT NULL
DROP TABLE #DeptEmp
CREATE TABLE #DeptEmp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DeptId int,
EmdId int,
)
--SET IDENTITY_INSERT #emp ON
--SET IDENTITY_INSERT #empDepend ON
--SET IDENTITY_INSERT #Dept ON
--SET IDENTITY_INSERT #DeptEmp ON
INSERT INTO #emp ( EmpLName, EmpFName)
select 'smith', 'jon' UNION ALL
select 'doe', 'jane' UNION ALL
select 'cooper', 'dd'
GO
INSERT INTO #empDepend ( EmdId, DepName)
select 1, 'Litte jonny smith' UNION ALL
select 2, 'little janny doe' UNION ALL
select 2, 'baby jimmy doe' UNION ALL
select 3, 'ee coopper' UNION ALL
select 3, 'ff coopper' UNION ALL
select 3, 'gg coopper'
GO
INSERT INTO #Dept (DeptId, DeptName)
select 1, 'Marketing' UNION ALL
select 2, 'Support' UNION ALL
select 3, 'Engineering'
GO
INSERT INTO #DeptEmp (DeptId, EmdId)
select 1, 1 UNION ALL
select 2, 2 UNION ALL
select 3, 3
GO
--SET IDENTITY_INSERT #emp OFF
--SET IDENTITY_INSERT #empDepend OFF
--SET IDENTITY_INSERT #Dept OFF
--SET IDENTITY_INSERT #DeptEmp OFF
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply