Tree Selection - 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

Tree Selection

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-16 : 09:45:13

Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:

NetworkId Full Name Sec Class Desc Sec Type Desc SecValue
tte Test Scenario Accounting Budget Center 142- ?

SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type

------------------

inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID

_____________________________________________________________________

What I need is the Description that are located in 4 other different tables that matches the Security Value from my first querie.

The result should look like this...

Network Id full Name Sec Class Desc Sec Type Desc Security Value
tst , Test Example , Accounting ,Budget Center , 142-Accountig dept

Thank you very much,

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-16 : 09:55:14

Here are example of the five individual selections for Security Value - They all work. My problem is adding all of then into the main querie.

Thanks!!!

Select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Budget_Center e on a.Security_Value = e.Budget_Center_ID

SELECT f.Company_Name
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Company f ON a.Security_Value = f.Company_ID

SELECT g.Enterprise_Description
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))

Select h.Business_Segment_Description
From dbo.Company_Hierarchy_Security a Inner Join
dbo.Business_Segment h on a.Security_Value = h.Business_Segment_ID

Select i.Team_Description
From dbo.Company_Hierarchy_Security a Inner Join
dbo.Team i on a.Security_Value = i.Team_ID

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-16 : 10:03:51

This is are my results now

NetworkId---- Full Name ---- Sec Class Desc----- Sec Type Desc ----SecValue
tte-------- Test Scenario---- Accounting --------Budget Center----- 142- ?
abcSmith-----ABC Smith-------Enterprise --------Company -----------444- ?

This is what I need:

Network Id---- full Name---- Sec Class Desc---- Sec Type Desc---- Security Value
tst --------- Test Example ---Accounting -------Budget Center ----142-Accountig dept
abcSmith-----ABC Smith-------Enterprise --------Company -----------444-Human Resource Dept.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 10:14:01

[code]SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description,tmp.Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
JOIN
(Select a.Network_ID, e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Value
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Budget_Center e on a.Security_Value = e.Budget_Center_ID

UNION ALL

SELECT a.Network_ID,f.Company_Name
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Company f ON a.Security_Value = f.Company_ID

UNION ALL

SELECT a.Network_ID,g.Enterprise_Description
FROM dbo.Company_Hierarchy_Security a INNER JOIN
dbo.Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))

UNION ALL

Select a.Network_ID,h.Business_Segment_Description
From dbo.Company_Hierarchy_Security a Inner Join
dbo.Business_Segment h on a.Security_Value = h.Business_Segment_ID

UNION ALL

Select a.Network_ID,i.Team_Description
From dbo.Company_Hierarchy_Security a Inner Join
dbo.Team i on a.Security_Value = i.Team_ID
)tmp
ON tmp.Network_ID=a.Network_ID
AND tmp.Value LIKE a.Security_Value + '%'[/code]

If you receive multiple matches from five queries how do you want result to be? As of now, it will return multiple records.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-16 : 10:17:32

Thank you Angel Guardian Visakh16 ---- Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources