May 1, 2012 at 4:17 am
dear friends,
i need your help.
i have one table and fields are
itemid name parentid
1 Root null
2 IT 1
3 sales 1
4 report 2
5 doc 4
now how can i query to find name doc and it should return me complete path like doc is under it/report.
means on result i want to show the parentid as complete path.
i hope you understand.
Thanks
May 1, 2012 at 4:29 am
Welcome to the best SQL Forum on earth!
In order to help you we will need a bit more details from you.
Table's DDL, some sample data (as insert script) and clearly outlined expected results would help us to understand your requirements and will guarantee the relevant help received in a short time.
Please follow the link at the bottom of my signature to find out what kind of details and in what form better to be provided when posting such questions.
May 1, 2012 at 5:21 am
I think this code will work for u.
Select r1.*,r4.Name+ '/' + r3.Name + '/' + r2.Name + '/' + r1.Name
From Roots r1
Inner Join Roots r2 on r1.ParentId=r2.ItemId
Inner Join Roots r3 on r2.ParentId=r3.ItemId
Inner Join Roots r4 on r3.ParentId=r4.ItemId
Where r1.ItemId=5
But this code will work correctly for "doc" only which u want. For other items u need to modify the code little bit.
Bye!
May 1, 2012 at 5:32 am
excellent
Thanks
May 1, 2012 at 5:37 am
Mohit,
your previous post really excellent,but i need some more help.
in the same table i have a column "isDirectory" datatype is bit(1/0).
in the same query i dont want to get in result if isDirectory has 1.
please help
Thanks
May 1, 2012 at 5:45 am
Add
WHERE IsDirectory <> Cast(1 as bit)
to the end of your query.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 1, 2012 at 6:10 am
Add this line in Where clause
r1.isdirectory!=1
Bye!
May 1, 2012 at 6:17 am
justmohit (5/1/2012)
Add this line in Where clauser1.isdirectory!=1
Bye!
Using != instead of <> is not recommended, as != is non-ISO standard (though it still works).
As the IsSubdirectory column is a Bit column, so you should use Cast(1 as bit) to avoid comparing bit with integer.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 1, 2012 at 6:27 am
Thanks all
it helped.
May 1, 2012 at 6:27 am
justmohit (5/1/2012)
I think this code will work for u.Select r1.*,r4.Name+ '/' + r3.Name + '/' + r2.Name + '/' + r1.Name
From Roots r1
Inner Join Roots r2 on r1.ParentId=r2.ItemId
Inner Join Roots r3 on r2.ParentId=r3.ItemId
Inner Join Roots r4 on r3.ParentId=r4.ItemId
Where r1.ItemId=5
But this code will work correctly for "doc" only which u want. For other items u need to modify the code little bit.
Bye!
Why not use a recursive CTE instead?
e.g.
--Create some sample data to play with
SELECT itemID, name, parentID
INTO #yourTable
FROM (VALUES(1, 'Root', null),(2, 'IT', 1),(3, 'sales', 1),
(4, 'report', 2),(5, 'doc', 4))a(itemID, name, parentID);
--Recursive CTE based on the sample data above
WITH CTE AS(
SELECT itemID, name, parentID, CAST(name AS VARCHAR(MAX)) AS [path]
FROM #yourTable
WHERE parentID IS NULL
UNION ALL
SELECT a.itemID, a.name, a.parentID, b.[path] + '/' + a.name
FROM #yourTable a
INNER JOIN CTE b ON a.parentID = b.itemID)
SELECT itemID, name, parentID, [path]
FROM CTE;
Which results in: -
itemID name parentID path
----------- ------ ----------- ---------------------
1 Root NULL Root
2 IT 1 Root/IT
3 sales 1 Root/sales
4 report 2 Root/IT/report
5 doc 4 Root/IT/report/doc
Then, you can use the same query for each level of the hierarchy instead of needing a new query every time. E.g., for "doc" you'd just add itemID =5 to the WHERE clause.
WITH CTE AS(
SELECT itemID, name, parentID, CAST(name AS VARCHAR(MAX)) AS [path]
FROM #yourTable
WHERE parentID IS NULL
UNION ALL
SELECT a.itemID, a.name, a.parentID, b.[path] + '/' + a.name
FROM #yourTable a
INNER JOIN CTE b ON a.parentID = b.itemID)
SELECT itemID, name, parentID, [path]
FROM CTE
WHERE itemID = 5;
May 1, 2012 at 6:48 am
justmohit (5/1/2012)
I think this code will work for u.Select r1.*,r4.Name+ '/' + r3.Name + '/' + r2.Name + '/' + r1.Name
From Roots r1
Inner Join Roots r2 on r1.ParentId=r2.ItemId
Inner Join Roots r3 on r2.ParentId=r3.ItemId
Inner Join Roots r4 on r3.ParentId=r4.ItemId
Where r1.ItemId=5
But this code will work correctly for "doc" only which u want. For other items u need to modify the code little bit.
Bye!
What would you do with a query with 64 levels? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2012 at 6:53 am
...
Using != instead of <> is not recommended, as != is non-ISO standard (though it still works).
...
I prefer to use "!=" over "<>". Is any reference material where it is "not recommended"?
BTW, that is standard for C-kind of languages
May 1, 2012 at 7:03 am
Eugene Elutin (5/1/2012)
...
Using != instead of <> is not recommended, as != is non-ISO standard (though it still works).
...
I prefer to use "!=" over "<>". Is any reference material where it is "not recommended"?
BTW, that is standard for C-kind of languages
Check out this link.
The fact that it is non-ISO and <> is was enough to sway me. The 'not recommended' comment was purely my opinion, based on this fact.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 1, 2012 at 7:09 am
Phil Parkin (5/1/2012)
Eugene Elutin (5/1/2012)
...
Using != instead of <> is not recommended, as != is non-ISO standard (though it still works).
...
I prefer to use "!=" over "<>". Is any reference material where it is "not recommended"?
BTW, that is standard for C-kind of languages
Check out this link.
The fact that it is non-ISO and <> is was enough to sway me. The 'not recommended' comment was purely my opinion, based on this fact.
I prefer <> but will always just stick to whichever is used by whoever is employing me.
AFAIK they're both supported by a lot of flavours (MSSQL, MySQL, PostgreSQL, Oracle and Informix) so portability is unlikely to be a swaying issue.
May 1, 2012 at 7:17 am
Eugene Elutin (5/1/2012)
...
Using != instead of <> is not recommended, as != is non-ISO standard (though it still works).
...
I prefer to use "!=" over "<>". Is any reference material where it is "not recommended"?
BTW, that is standard for C-kind of languages
Yep, it's not ISO, but it's totally accepted in MS SQL Server T-SQL.
The only difference it can possibly make is in code portability.
If you thinking of moving your code into ISO-compliant database management system, then it will (may) not work.
However, personally, I never heard about any DMS which can be called 100% ISO-compliant. And, I guess, the problem with comparison operators will be the minor one from the list of other non-portable things...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply