SELF JOINS PLEASE HELP! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELF JOINS PLEASE HELP!

Author

Topic

PavanS86
Starting Member

2 Posts

Posted - 2011-10-02 : 15:50:59

Table: Employee

Employee Hierarchy:
CEO
Director
Manager
Employee

I need to retrieve information of Directors who manage at least 2 managers, who in turn manage at least 2 Employees.

So Far this is what I have:

CREATE TABLE Employee (
EmpID INT,
Name VARCHAR(15),
Title VARCHAR(10),
MgrID INT

)

GO

INSERT INTO Employee VALUES (100, 'Pav', 'CEO', NULL)
INSERT INTO Employee VALUES (200, 'Dave', 'DRT1', 100)
INSERT INTO Employee VALUES (300, 'Mike', 'DRT2', 100)
INSERT INTO Employee VALUES (400, 'Ron', 'DRT3', 100)
INSERT INTO Employee VALUES (500, 'Snok', 'MG1', 200)
INSERT INTO Employee VALUES (600, 'Niki', 'MG2', 200)
INSERT INTO Employee VALUES (700, 'Deen', 'MG3', 200)
INSERT INTO Employee VALUES (800, 'Jen', 'MG4', 300)
INSERT INTO Employee VALUES (900, 'Vin', 'MG5', 400)
INSERT INTO Employee VALUES (1000, 'Dan', 'MG6', 400)
INSERT INTO Employee VALUES (1100, 'Don', 'E1', 500)
INSERT INTO Employee VALUES (1200, 'Jon', 'E2', 500)
INSERT INTO Employee VALUES (1300, 'Bun', 'E3', 600)
INSERT INTO Employee VALUES (1400, 'Duh', 'E4', 700)
INSERT INTO Employee VALUES (1500, 'Lug', 'E5', 700)
INSERT INTO Employee VALUES (1600, 'Bug', 'E6', 700)
INSERT INTO Employee VALUES (1700, 'Fug', 'E7', 800)
INSERT INTO Employee VALUES (1800, 'Mug', 'E8', 800)
INSERT INTO Employee VALUES (1900, 'Dug', 'E9', 900)
INSERT INTO Employee VALUES (2000, 'Sam', 'E10', 1000)
INSERT INTO Employee VALUES (2100, 'Lam', 'E11', 1000)

/* Half of question's Condition met, only retrieves Managers with managing at least 2 leaf-level employees */

SELECT M.EmpID AS 'MgrID', M.Title AS 'Mgr Title', COUNT(*), Ex.EmpID, Ex.Title
FROM Employee E
INNER JOIN Employee M
ON E.MgrID = M.EmpID
INNER JOIN Employee Ex
ON M.MgrID = Ex.EmpID
WHERE Ex.MgrID IS NOT NULL
GROUP BY M.EmpID, M.Title, Ex.EmpID, Ex.Title
HAVING COUNT(*) >= 2

This is where Im stuck! Im not able to restrict the query to an Executive who only manages two managers.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-02 : 19:39:14

if the hierarchy is limited on 4 level and title can be classified by value (CEO,DRT,MG,E ...) as your sample data presents, I think you can use subquery to find out the DRT.

Directors who manage at least 2 managers, who in turn manage at least 2 Employees
if I understand your requirement correctly, the expected output should be : 200, 'Dave', 'DRT1', 100.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 21:13:31

[code]
select d.EmpID, d.Name
from Employee d
inner join
(
select m.EmpID, m.Name, m.MgrID
from Employee m
inner join Employee e on m.EmpID = e.MgrID
group by m.EmpID, m.Name, m.MgrID
having count(*) >= 2
) m on d.EmpID = m.MgrID
where d.Title like 'DRT%'
group by d.EmpID, d.Name
having count(*) >= 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:25:15

quote:

Originally posted by khtan


select	d.EmpID, d.Name
from	Employee d
	inner join
	(
		select	m.EmpID, m.Name, m.MgrID
		from	Employee m
			inner join Employee e	on	m.EmpID	= e.MgrID
                where m.Title like 'MG%'
		group by m.EmpID, m.Name, m.MgrID
		having count(*) >= 2
	) m	on	d.EmpID	= m.MgrID
where	d.Title	like 'DRT%'
group by d.EmpID, d.Name
having count(*) >= 2

KH
[spoiler]Time is always against us[/spoiler]

shouldnt it have extra condition as above inside to make sure you've only considering managers inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 01:26:46

Yes. no harm having additional condition.

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 01:30:19

ok..just checked...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-03 : 04:05:06

With Common table Expression :


With CTE as (
				Select m.EmpID, m.Name, m.MgrID from Employee m
				Inner Join Employee e on m.EmpID=e.MgrID
				where m.Title like 'MG_'
				Group By m.EmpID, m.Name, m.MgrID
				Having COUNT(*)>=2)
Select D.EMPID,D.Name
from CTE 
Inner Join Employee D on CTE.mgrID=D.EmpID
where d.Title	like 'DRT%'
Group By D.EMPID,D.Name
Having COUNT(*)>=2

PavanS86
Starting Member

2 Posts

Posted - 2011-10-03 : 12:32:53

Thanks for the help Guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources