asked
How to get many subqueries in one query
How do a do a query of one table where it has job numbers that are all unique a MasterJob column. There is one Master job and maybe 2 to 40 jobs with that job as their MasterJob then under those jobs there will be jobs with the sub job as its master. What I want is to pull a list of ever job that would be under the master job which could be 4 or more levels deep of sub jobs. is this possible.
Let's assume that you have a table which has a self-referencing relationship. For example, there is a column named JobNumber and also there is a column named MasterJob. Every record representing a job references its respective master job. It might be possible for any specific job to be standalone (not having a master job to which it belongs in which case there is NULL in the MasterJob column for such record). It is also possible for any job to have child jobs which can in turn be parents to some other jobs. The formal name of such design is "adjacent list" or "adjacency list". It is essentially representing the hierarchical data which may have dynamic number of levels depending on the actual data. If all you need is the list of all jobs which are ultimately related to some specific master job then one way to get the results you need is via recursive CTE (common table expression). If the database in question is Oracle then there is a "connect by" clause to get the same results. Here is the T-SQL script which uses recursive [common table expression][1] (CTE). It includes the Path column just to see the hierarchy path of all returned records: ;with cte as ( select JobNumber, MasterJob, isnull(cast(MasterJob as varchar) + '/', '') + cast(JobNumber as varchar(max)) BomPath from YourTable anchor where MasterJob is null /* use the condition here, such as specific job master value */ union all select t.JobNumber, t.MasterJob, BomPath + '/' + cast(t.JobNumber as varchar) from cte inner join YourTable t on cte.JobNumber = t.MasterJob ) select * from cte order by BomPath; Just plugin the specific master job value to see all its children, grandchildren, etc. There is a great alternative to the "adjacency list" design, which is called "nested sets" which allows huge improvements in performance of the queries to return hierarchical data. Please read the articles by @Jeff Moden published on the
sqlservercentral.com website, these are a **must read** if you want to have a good understanding of the hierarchical data: - [Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets][2] - [Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations][3] Hope this helps. Oleg [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql [2]:
http://www.sqlservercentral.com/articles/Hierarchy/94040/ [3]:
http://www.sqlservercentral.com/articles/T-SQL/94570/
@irishrocks Yes, it will go down many levels until the recursion limit is met. By default, it is 100 which I believe is well more then enough for your data. The limit can be raised by the means of adding the maxrecursion specification. For example, to raise the limit to 200 add the line reading option (maxrecursion 200) to the bottom of the select statement (last line of the query in the answer). I am sure that the data you have does not have the sub-jobs going more than, or anywhere near, 100 levels deep, so this specification is not necessary as the default will work just fine. Usually, the option is used during tests of the initial query to ensure that nothing went wrong and the way the query is written does not fall into infinite recursion trap. Please read the [common table expression page][1] in Microsoft docs, it has a thorough explanation of how CTE works, and many examples. [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql
That worked great just curious if this will go down many levels. This is the longest one I get. 000000000061860/000000000061861/000000000061862/000000000061863 I'm assuming it will go as deep as I need it. I will look at the other stuff i'm a newbi to SQL on have to use it every so often so this helps alot.
18 People are following this question.
