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] (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] - [Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations] Hope this helps. Oleg :