|
I have Employees and Reporting hirarchy.For Ex:
1
2 3 4
5 6 7 8
Employees 2,3,4 report to 1 and 5,6 report to 2 and 7,8 report t0 4.
If I pass 1 to query it results
2,3,4,5,6,7,8
2 then 5,6
3 then 7,8
How can i get this in sqlserver 2005
|
|
|
|
|
What did you so far for your query?
Use CASE switch to archive your query.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I have thousands of records how can I use CASE ?
|
|
|
|
|
|
I have only two columns like below.
id id_project id_parent
-------------------------
1 root root
2 abc123 root
3 xyz098 root
4 cmd003 xyz098
5 asd874 abc123
6 f8jk12 cmd003
So, I will need the output to look similar to this when pass id as '1'
- abc123
-- asd874
- xyz098
-- cmd003
--- f8jk12
So, I will need the output to look similar to this when pass id as '3'
- cmd003
-- f8jk12
So, I will need the output to look similar to this when pass id as '2'
- asd874
How can I do this in sqlserver2005
|
|
|
|
|
Did you read the article i linked you to?
|
|
|
|
|
To get result, you need to write recursive function
A function to get Parent
CREATE FUNCTION dbo.FindRoot(@id int)
RETURNS int
AS
BEGIN
DECLARE @Id_parent int
SELECT @Id_parent= Id_parent
FROM TableName
WHERE id = @id
WHILE @Id_parent <> NULL
BEGIN
SELECT @id = @Id_parent
SELECT @Id_parent = Id_parent
FROM PrimeInfo
WHERE id = @id
END
RETURN @id
END
and then store procedure to extract your data by providing id value
CREATE PROCEDURE BuildTree(@id int)
AS
SET NOCOUNT ON
CREATE TABLE #results(level int, id int, id_parent int)
DECLARE @id_parent int
DECLARE @level int
SELECT @level = 1
DECLARE @root int
SELECT @root = dbo.FindRoots(@id)
CREATE TABLE #stack (id int, level smallint)
INSERT INTO #stack VALUES (@root, @level)
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @id = s.id, @id_parent= IsNull(t.id_parent, 0)
FROM #stack s INNER JOIN TableName t
ON t.id = s.id
WHERE level = @level
INSERT INTO #results VALUES (@level, @id, @id_parent)
DELETE FROM #stack
WHERE level = @level
AND id = @id
INSERT #stack
SELECT id, @level + 1
FROM TableName
WHERE id_parent = @id
IF @@ROWCOUNT > 0
BEGIN
SELECT @level = @level + 1
END
END
ELSE
BEGIN
SELECT @level = @level - 1
END
END
SELECT id, id_parent, level FROM #results
|
|
|
|
|
I would use a cursor to step through each level of the hierarchy and insert the results into a temp table.
Then just select from the temp table at the end.
|
|
|
|
|
Can you post query how to step through each level
|
|
|
|
|
Actually, a recursive CTE is probably your best bet.
|
|
|
|
|
Here's an example that probably does exactly what you want:
Recursive CTE[^]
|
|
|
|
|
I tried below stored procedure. I am getting the result what I want but its print statement. How can I convert this into table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Insert it into a temp table and then select * from that table at the end.
|
|
|
|
|
I did that ... As everytime sp excutes output displays like multiple tables but not in one table
|
|
|
|
|
Have you tried using a CTE or a cursor?
I reckon those are your best bet and probably what your teacher is looking for if this is a homework question.
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
This is the sp I have tried. I am getting what I want but in print statement. How to convert it into Table.
ALTER PROC [dbo].[spa_R_TeamDetails]
@ReportsTo INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @PersonId INT, @DisplayName VARCHAR(100)
SET @DisplayName = (SELECT DisplayName+'~'+CONVERT(VARCHAR,PersonId)+'~'+OrgEmpId TeamMembers
FROM Person WHERE PersonID = @ReportsTo)
PRINT REPLICATE('- ', (@@NESTLEVEL * 1)-1) + @DisplayName
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo)
WHILE @PersonId IS NOT NULL
BEGIN
EXEC spa_R_TeamDetails @PersonId
SET @PersonId = (SELECT MIN(PersonId) FROM Person WHERE ReportsTo = @ReportsTo AND
PersonId > @PersonId)
END
END
-- spa_R_TeamDetails 1781
|
|
|
|
|
Have a look to this code and see whether it solves your problem,
CREATE TABLE TempTree (Id int IDENTITY, Id_Project VARCHAR(100), Id_Parent VARCHAR(100))
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Root','Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 1', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 2', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 1 3', 'Root')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 1', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 2', 'Level - 1 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 3', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 4', 'Level - 1 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 5', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 2 6', 'Level - 1 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 1', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 2', 'Level - 2 1')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 3', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 4', 'Level - 2 2')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 5', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 6', 'Level - 2 3')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 7', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 8', 'Level - 2 4')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 9', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 10', 'Level - 2 5')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 11', 'Level - 2 6')
INSERT INTO TempTree (Id_Project, Id_Parent) VALUES ('Level - 3 12', 'Level - 2 6')
CREATE PROC Dbo.Proc_TheTree (@parent VARCHAR(100))
AS
CREATE TABLE #TheList (RootId int, RootName VARCHAR(100), ChildName VARCHAR(100))
CREATE TABLE #TheSearch (SLNO INT IDENTITY, ParentName VARCHAR(100), IsSearchCompleted BIT)
IF NOT EXISTS (SELECT * FROM TempTree WHERE id_parent = @parent)
BEGIN
SELECT * FROM TempTree WHERE id_project = @parent
END
ELSE
BEGIN
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @parent
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @parent), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @parent
DECLARE @MINSLNO INT
DECLARE @PARENTNAME VARCHAR(100)
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch
WHILE ISNULL(@MINSLNO,0) > 0
BEGIN
SELECT @PARENTNAME = ParentName FROM #TheSearch
WHERE SLNO = @MINSLNO AND ParentName <> @parent
IF EXISTS (SELECT * FROM TempTree WHERE id_parent = @PARENTNAME)
BEGIN
INSERT INTO #TheList (RootId, RootName, ChildName)
SELECT (SELECT Id FROM TempTree WHERE Id_Project= @PARENTNAME), Id_Parent, Id_Project
FROM TempTree
WHERE id_parent = @PARENTNAME
INSERT INTO #TheSearch (ParentName, IsSearchCompleted)
SELECT id_project, 0 FROM TempTree WHERE id_parent = @PARENTNAME
END
SELECT @MINSLNO = MIN(SLNO) FROM #TheSearch WHERE SLNO > @MINSLNO
END
SELECT * FROM #TheList
END
EXEC Proc_TheTree 'Level - 1 1'
|
|
|
|
|
I'm only a very occasional SQL user, so I'm not sure if this is even possible - but if it is, can someone help me with the SQL?
The situation:
In Table 1, I have a list of components, each with an ID and a type.
In Table 2, I have a mapping between Outlet components and Nozzle components (two columns with component IDs in both, lets call them Out_IDs and Nozz_IDs, an Outlet may be linked to many nozzles).
I'm trying to find all the Nozzle components that are not linked to any outlets.
So in pseudo-SQL:
SELECT * FROM Components WHERE (Component.[Type] = Nozzle)
AND (Component.[ID] Is Not One Of Mapping.[Nozz_IDs])
It's the AND... bit I can't figure out, probably some sort of JOIN I can't fathom.
Thanks all,
Dan
|
|
|
|
|
Bah! Of course I figure it out as soon as I ask for help!
SELECT * FROM Components LEFT JOIN Nozzles ON Components.[Component Key] = Nozzles.[Nozzle Key]
WHERE (((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null));
|
|
|
|
|
Ain't is the way.
Just some advice.
Notice that you need [] around column names with spaces and special characters, eliminate these so [Component Key] becomes ComponentKey , life will be much more pleasant.
Also NEVER use key words in a column name (Key and Database) I know these will not be key words when concatenated but it still applies.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the advice, I'll bear it in mind for the future. Unfortunately, I've inherited this MDB structure based on an old Fortran record structure so changing the names is not so easy!
|
|
|
|
|
Can you see anything wrong with this?
CDaoDatabase* pDB = static_cast<CMainFrame *>(AfxGetMainWnd())->GetDatabase();
CMyRecords Records(pDB);
CString sSQL = "SELECT * FROM Components LEFT JOIN Nozzles ON
Components.[Component Key] = Nozzles.[Nozzle Key] WHERE
(((Components.[Database Number])=15) AND ((Nozzles.[Nozzle Key]) Is Null))";
try
{
Records.Open(dbOpenDynaset, sSQL, 0);
if (Records.IsBOF() || Records.IsEOF())
{
return;
}
Records.MoveFirst();
while (!Records.IsEOF())
{
if (Records.CanUpdate())
{
Records.Delete();
}
Records.MoveNext();
}
Records.Close();
}
catch(CDaoException *e)
{
e->ReportError();
e->Delete();
if (Records.IsOpen())
{
Records.Close();
}
}
I can see it gets the right records, it doesn't give anY errors or exceptions, but it also doesn't delete the records!
Must be missing something...
|
|
|
|
|
Without going into the code the first point I would make is that deleting a record while inside a collection changes the collection. So you move to the first record and delete it, you collection hanged and where am I supposed to move from.
Try starting from the last record in the collection, delete that and move forward 1 record, or go to the last record in the collection. Basically change the way your loop works.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
DaoRecordsets don't work like that - presumably for the very reason you describe! 'Delete' simply marks a record as deleted and doesn't alter the record order. this also allows you to rollback changes if necessary.
It's all working now though, so thanks for your input!
|
|
|
|
|