Created
February 5, 2015 14:22
-
-
Save ronmichael/bad0d49a72ba7d4ecafb to your computer and use it in GitHub Desktop.
Return all parent records in SQL hierarchy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
if you have a table of rows with parent rows, this will return one row for each row and each of its parents. | |
thanks to http://stackoverflow.com/questions/13487006/use-sql-server-cte-to-return-all-parent-records. | |
*/ | |
with items(uniqueid,parentid) as ( | |
select uniqueid, uniqueid | |
from equipment | |
union all | |
select items.uniqueid, e.parentid | |
from equipment e | |
inner join items on items.parentid = e.uniqueid | |
) | |
select * from items | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment