Hierarchy Query question - 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

General SQL Server Forums

New to SQL Server Programming

Hierarchy Query question

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-12-06 : 17:30:46

Table Structure

Table Name: TBLFORMAT
ID
NAME
JOBDESC

Table Name: AIT_HT_LIVE
ID
MGR_LVL1

I have a query that captures the people reporting directly under a specific person

SELECT AIT_TBLFORMAT.ID, AIT_TBLFORMAT.NAME, AIT_TBLFORMAT.JOBDESC AS TITLE,

FROM AIT_HT_LIVE INNER JOIN AIT_TBLFORMAT ON AIT_TBLFORMAT.ID = AIT_HT_LIVE.ID
WHERE (AIT_HT_LIVE.MGR_LVL1 = 212)
AND AIT_TBLFORMAT.ID IN (SELECT MGR_LVL1 FROM AIT_HT_LIVE WHERE MGR_LVL1 IN (SELECT MGR_LVL1 FROM AIT_HT_LIVE))

The above query shows only the 4 people reporting under person of id (212) Those 4 people however have people reporting under them who are managers as well – Is there a way to tweak this query so I can capture all people reporting under 212 who are managers ?

Thanks again

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-06 : 18:55:39

Are the ID columns the only relationship bettween those two tables? Is there are self-reference on any of the tables? How do you determine who is a manager, by virtue of a row in the AIT_HT_LIVE table?

Do you have sample data you can post in a consumable format with expected results? Here is a link that can help with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you want, you can investigate Recursive CTEs. I think that is what you need here.

rc1138
Starting Member

35 Posts

Posted - 2010-12-06 : 21:08:02

Hi and Thanks for the reply

TBLFORMAT

CREATE TABLE [dbo].[AIT_TBLFORMAT](
[ID] [varchar](3) NULL,
[NAME] [varchar](50) NULL,
[JOBDESC] [varchar](50) NULL,

AIT_HT_LIVE

CREATE TABLE [dbo].[AIT_HT_LIVE](
[ID] [varchar] (3) NOT NULL,
[MGR_LVL1] [int] NULL

Managers are determined by an earlier stored procedure implemented earlier. ID's of managers are stored in the AIT_HT_LIVE table in the
'MGR_LVL1' column

So lets say these are the data in the tables

AIT_TBL_FORMAT
ID||||NAME|||JOB_DESC
121|||John|||SR.Analyst
132|||Alex|||BA
143|||Kaye|||BA
154|||Dean|||Developer
212|||Casey|||SR. BA
111|||Alej|||Analyst
112|||Jean|||JR.Analyst

AIT_HT_LIVE
ID||||MGR_LVL1
121||||212
132||||212
143||||212
154||||212
111||||121
112||||111

So with the query mentioned earlier

SELECT AIT_TBLFORMAT.ID, AIT_TBLFORMAT.NAME, AIT_TBLFORMAT.JOBDESC AS TITLE,

FROM AIT_HT_LIVE INNER JOIN AIT_TBLFORMAT ON AIT_TBLFORMAT.ID = AIT_HT_LIVE.ID
WHERE (AIT_HT_LIVE.MGR_LVL1 = 212)
AND AIT_TBLFORMAT.ID IN (SELECT MGR_LVL1 FROM AIT_HT_LIVE WHERE MGR_LVL1 IN (SELECT MGR_LVL1 FROM AIT_HT_LIVE))

I seem to only get the 4 direct managers under associate 212. I need help regarding the logic on how to include associate 111 as 112 reports under 111 - I will definately read about the recursive CTE's however
thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources