|
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!
|
|
|
|
|
The MessageBoard fairies strike again!
If I change the SQL to the much simpler:
"SELECT * FROM Components WHERE (Components.[Database Number])=15 AND Components.[Component Key] NOT IN (SELECT [Nozzle Key] FROM Nozzles)"
it all works.
|
|
|
|
|
Hi!
I've to read each name in a field from a table and do some string mangling and update each name into another field. I've to query the database from C++. I'm using SQLite. I used the following code to do this:
SQLManager SQLdb;
SQLdb.init("../../data/config/playerDatabase.db");
array<stringc> ctyLst = SQLdb.Query("Select DISTINCT Team from Test ORDER BY Team DESC");
array<stringc> playerLst;
core::stringc queryStr,updateTest;
for(u32 i = 0; i<ctyLst.size();i++)
{
queryStr = L"Select Name From Test WHERE Team ='";
queryStr += ctyLst[i].trim();
queryStr += L"'";
array<stringc> playerTmpLst;
playerTmpLst = SQLdb.Query(queryStr);
for(u32 j = 0; j < playerTmpLst.size(); j++)
{
playerLst.push_back(playerTmpLst[j].c_str());
}
}
I'm doing string mangiling an updating as follows:
for(int j = 0; j<playerLst.size(); j++)
{
updateTest = L"UPDATE Test SET MangledName ='";
unsigned int t = time(NULL);
srand(t);
playerLst[j] = playerLst[j].trim();
int index = playerLst[j].findLast(' ');
stringc subStr1 = playerLst[j].subString(0,index);
stringc subStr2 = playerLst[j].subString(index+1,15);
subStr1.append(" ",1);
subStr1.append(swapChars(subStr2).c_str(),subStr2.size());
updateTest += subStr1.trim();
updateTest += L"'";
SQLdb.Query(updateTest);
Sleep(10);
cout<<subStr1.c_str()<<endl;
t++;
}
While printing the result to a file, it prints correctly. But only the last name in the array is updated to all the records in the field. How to Select a single name at a time and do the string mangling and update the name to the database?
|
|
|
|
|
One normally uses a WHERE clause to specify which record(s) get updated.
Remarks:
- what is the purpose of Sleep(10)?
- what would happen if a player's name contained a quote?
- why don't you use SqlParameters instead of string concatenation?
|
|
|
|
|
plzz give me the code for inserting the data in the database through msAccess DataBase.
|
|
|
|
|