I have a table with a parent, child, and grandchild relationship. Can anyone help me with a query that will return the child and grandchild of a parent?
Heres my table:
id pid name
--------------------------------
1 0 UntID
2 0 Vin Number
3 0 Make
4 3 Model
5 4 Model Number
6 0 Model Year
7 0 Vehicle Type
8 0 Odometer Miles
When I select 3 as the id I need these results:
id pid name
--------------------------------
3 0 Make
4 3 Model
5 4 Model Number
I need to write a sql that generate the hierarchy in an organization.
Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
I need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.
Whoever joining new to org the hierarchy
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree ( @departmentItemId int ) RETURNS TABLE AS RETURN with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
[code].....
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL, ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below) IsActive bit NOT NULL DEFAULT ((1)),
The above table shows a parent child relationship with the hierarchy shown in column level. for each parent (IDs 1, 9, 11), I want the first child level where the column IsAutoCalculate = 0
so for parent Id 1, the rows to be returned is of level 1 as that is the first child row of this parent with IsAutoCalculate = 0. The rows with level 2 should not be returned
For parent id 3, the rows to be returned will be with level 2 as this is the first child row of this parent with IsAutoCalculate = 0
I need to write a sql that generate the hierarchy in an organization.Below an example
emplid empname supervisor_id superv_name 1 subu null null 2 vid 1 sub 3 ram 4 satis 4 satis 2 vid
i need an output to this query as below and also one important the supervisor ie supervisor_id and name is null is the top level,every employee also has to report to him and also to his all above supervisors.whoever joinng new to org the hierachy should be follwed
empid empname supervisor_id superv_name 3 ram 4 satis 4 satis 2 vid 2 vid 1 subu 4 satis 1 subu 3 ram 1 subu 3 ram 2 vid 5 kumar 1 subu 5 kumar 4 satis 5 kumar 2 vid 1 subu null null
I am in the process of develping a MSRS report using an MSAS 2005 OLAP cube as my data source. In the MSRS Query Builder, I am using an MDX query which successfully executes in Management Studio. Something like the following:
SELECT
NON EMPTY { [Measures].[Fact Count] } ON COLUMNS, NON EMPTY{ ( [Dim Attribute].[Hierarchy Not Visible].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [MyCube]
The twist is that the AttributeHierarchyVisible property of the [Dim Attribute].[Hierarchy Not Visible] is set to False.
As mentioned previously, the query successfully executes in Management Studio. However when it is executed in the MSRS Query Builder, the following error message is displayed:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension.. Parameter name: mdx (MDXQueryGenerator)
Is there a way to successfully query dimension attributes whose hierarchies are not visible?
I have one view, i written below query to get employee hierarchy based on orgid and employee name..
If i select employeename it shoukd show employee reporties(under employees and below employee reporties like tree structure)
It is running fast in SSMS Level, but it is taking more time in SSRS(Sql Sever Report Services), If i run this query in SSRS,Some times System not responding.Â
Declare @OrgId int Declare @EName varchar(30) Set @OrgId=56793 Set @EName='ABCD' Select EmpId
For some reason, I run a stored procedure in Query Analyzer and it works fine. When I run the very same procedure in MS access by clicking on its link I have to run it twice. The first run gives me the message that the stored procedure ran correctly but returned no records. The second run gives me the correct number of records but I have to run it twice. I am running month-to-month data. The first run is Jan thru March. Jan and Feb have no records so I run three months on the first set. The ensuing runs are individual months from April onward. The output is correct but any ideas on why I have to do it twice in Access? I am a bit new to stored procedures but my supervisor assures me that it should be exactly the same.
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
End Sub /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I executed the code successfully and I got a box which asked for "Enter the query string". I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
How to optimize the following Stored procedure running on MSSQL server 2000 sp4 :
CREATE PROCEDURE proc1 @Franchise ObjectId , @dtmStart DATETIME , @dtmEnd DATETIME AS BEGIN
SET NOCOUNT ON
SELECT p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec , count(1) "Count" , sum(Amount) "Total" FROM tb_Event t JOIN tb_Prod p ON ( t.ProdId = p.ProdId ) JOIN tb_ACDef a ON ( t.ACDefId = a.ACDefId ) JOIN tb_Curr c ON ( t.CurrId = c.CurrId ) JOIN tb_Event e ON ( t.EventId = e.EventId ) JOIN tb_Setl s ON ( s.BUId = t.BUId and s.SetlD = t.SetlD ) WHERE Fran = @Franchise AND t.CDate >= @dtmStart AND t.CDate <= @dtmEnd AND s.Status = 1 GROUP BY p.Product , c.Currency , c.Minor , a.ACDef , e.Event , t.Dec
HI! I am new in DB so I need some advices for finding the right solution. I need to be able to make automatically any join between tables which the user choose and deliver the result. is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.
if u have any idea about how can I manage this, pls help me.
I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:
Net Profit .....Gross Profit ..........Revenue ..........Direct Costs .....Indirect Expense
Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:
Expenses .....Node a ..........Child a (Indirect Expense Type) .....Child b (Direct Cost type) Net Sales Adjustments .....Child a (Revenue type) .....Child b (Revenue type)
Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:
Net Profit ....Gross Profit ........Revenue ................Net Sales Adjustments ....................Child a (Revenue type) ....................Child b (Revenue type) ........Direct Costs ...........Expenses .................Child b (Direct Cost type) ........Indirect Expense ............Expenses .................Node a .....................Child a (Indirect Expense Type)
Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:
Expenses .....Node a ...........Child a (Indirect Expense Type) .....Child b (Direct Cost type)
Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.
I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.
Hai everyone., i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle .. plz help me on this..
for example: id | FName |parentid | 1 | sandy | 2 | robert| 1
if i give the parentid 1 in where condition of a query i need the details of 'sandy'
Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?
i tried on CTE but iam not gettng correct result plz help me
Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int) Insert into @Tmaster Select 1, 'Fixed', 14 Union All Select 2, 'Flexible',14 union All Select 3, 'Others',14 union All Select 13, 'Retirement benefits',1 union All Select 14, 'PBHeads', 0
Select * From @Tmaster
Declare @TDetails Table ( MasterId Int, Description varchar(100)) Insert into @TDetails Select 1, 'Basic' union all Select 1,'Conveyance' union all Select 1,'HRA' union all Select 1,'Special Allowance' union all Select 2, 'Children Education' union all Select 2,'Travel Allowance' union all Select 2,'Medical Expenses' union all Select 2,'Variable Spl Pay' union all Select 3, 'Project Allowance' union all Select 3,'Quarterly Incentive' union all Select 3,'Shift Allowance' union all Select 3,'Annual Component' union all Select 13,'Provident Fund' union all Select 13,'Gratuity' union all Select 13,'Super annuation'
Select * From @TDetails
in group list we have to show fixed, flexible, other in subgroups(L1) list under fixed we have to show Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits
in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits
output : Group Subgroup(L1) Subgroup(L2) Fixed Basic salary HRA Conveyance Special Allowance Retirement benefits Provident Fund Gratuity Super annuation
Flexible reimbursements LTA/Group Travel Medical expenses Children education variable special pay
Others Project allowance Performance incentive Medical insurance
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName FROM CATEGORY LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values ID Name Parent_Category_id 1 Pens 2 Animals 3 Rocks 4 Horses 2 5 Dogs 2
When I currently run the query I get: Name TotalStock Pens 20 Animals 30 Rocks 40 Horses 50 Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
I have structure:FolderId, FolderName, ParentFolderId, FullPathe.g.1, First, null, First2, Sec, 1, First/Sec.....When update happens to FolderName, i need to update all FullPaths thatare below Folder which is changing.Any ideas?Thank you.
Greetings!I could really use some suggestions on how to improve on thefollowing, it at all possible:Table 'Customer'---------------------ID GUID PK....Table 'Facility'-----------------ID GUID PKCustomerID GUID FK (FK to Customer GUID)....Table 'Rate'----------------ID PKOwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)OwnerLevel INT Contraint 1-3<Rate Data>Table 'Rate' is a sparse hierarchy of data. There are 3 possiblelevels in the hierarchy as follows:OwnerID <NULL>OwnerLevel 1This indicates Global rate data.OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data.OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data.Now, a given Customer need not have an entry in the Rate table. If aCustomer does not have an entry, it is supposed to 'inherit' Globalrate data. A given Facility need not have an entry in the Rate table.If a Facility does not have an entry, it is supposed to inheritCustomer-specific rate data, and in the absence of an entry for theFacility's parent Customer, it is supposed to inherit Global ratedata.The challenge is that I want to write a view to give me back theappropriate rate record for Customer and Facility. Here's what I'vedone so far.View _Rate--------------SELECTRate.*,NULL AS TargetIDFROMRateWHERERate.OwnerID IS NULLUNIONSELECTRate.*,Customer.ID AS TargetIDFROMRateCROSS JOINCustomerWHERERate.OwnerID IS NULLOR Rate.OwnerID = Customer.IDUNIONSELECTRate.*,Facility.ID AS TargetIDFROMRateCROSS JOINFacilityWHERERate.OwnerID IS NULLOR Rate.OwnerID IN (Facility.CustomerID, Facility.ID)View view_Rate--------------------SELECT_Rate.*FROM_RateINNER JOIN(SELECTTargetID,MAX(OwnerLevel) AS OwnerLevelFROM_RateGROUP BYTargetID) AS Filtered_RateON_Rate.TargetID = Filtered_Rate.TargetIDAND _Rate.OwnerLevel = Filtered_Rate.OwnerLevelThe combination of these two views gives a resultset that contains 1record for every Target ID as follows:TargetID <NULL>OwnerID <NULL>OwnerLevel 1This indicates Global rate data established at the Global level.TargetID <Customer.ID>OwnerID <NULL>OwnerLevel 1This indicates Customer rate data for the specific Customer identifiedby Customer.ID is inherited from the Global rate data.TargetID <Customer.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data for the specific Customeridentified by Customer.ID (not inherited).TargetID <Facility.ID>OwnerID <NULL>OwnerLevel 3This indicates Facility rate data is inherited from the Global ratedata.TargetID <Facility.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Facility rate data for the specific Facility identifiedby Facility.ID is inherited from the Facility's parent Customer'sCustomer-specific rate data.TargetID <Facility.ID>OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data for the specific Facilityidentified by Facility.ID (not inherited).I know this is a lengthy post, and a complicted query scenario, butI'm not willing to accept that my solution is the best solution justyet. Please consider that I really need this functionality in a VIEWas much as possible.Thank you for your learned consideration.I eagerly await your replies.Darryll
Hi everybody, I've been reading the book MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits) as a part of my preparation for exam 70-441 The book is really clear, and I could understand everything easily because I've some experience as a database developer. Until now, there's only one thing that I didn't fully understand: On pages 53 and 54, it mentions a type of hierarchy called: a graph hierarchy, and it gives a small example made the things even more complicated I've many books about database design theories, but they don't use the term (graph hierarchy) any where... So, I appreciate if you can point me to an online resource or a book that discusses this term thorougly.
Last time I worked with SSAS and build a Cube. Because I€™m now very happy with the front-end excel 2003 or excel 2007 I thought I build my own Report with SSRS. Now there is something I don€™t understand: I build a Hierarchy in SSAS that a want to use in SSRS. Is there a chance to use it without any features? Have I to use parameters or something another? In my opinion it makes any sense to build a hierarchy new, because it€™s already exists in SSAS.
I'm facing a complicated problem and I don't think that the solution will be an easy one.
I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.
select lpad(' ',5*(orderid)) || to_char(descr) as menui from <table _name> where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6') start with <item_parent='item_parent' > connect by prior <menu_item = item_parent and menu_name='something'; > ?(condition)
Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?
And I specify that I want all child items below the Id 2, it must return: Id | Title | Url | ParentId | Depth 2 | Sub-Test | http://localhost/subtest | 1 | 0
Hello to all, I have a stored procedure. If i give this command exce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted. but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time I don't know why? Maybe can somebody help me? thanks in million best Regards Pinsha My Procedure Codes are here:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[ShortestPath] (@IDMember int, @IDOther varchar(1000),@Level int, @Path varchar(100) = null output ) AS BEGIN
if ( @Level = 1) begin select @Path = convert(varchar(100),IDMember) from wtcomValidRelationships where wtcomValidRelationships.[IDMember]= @IDMember and PATINDEX('%'+@IDOther+'%',(select RelationshipIDs from wtcomValidRelationships where IDMember = @IDMember) ) > 0 end if (@Level = 2) begin select top 1 @Path = convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and PATINDEX('%'+@IDOther+'%',B.RelationshipIDs) > 0 end if (@Level = 3) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',C.RelationshipIDs) > 0 end if ( @Level = 4) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',D.RelationshipIDs) > 0 end if (@Level = 5) begin select top 1 @Path = convert(varchar(100),A.IDMember)+ '-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember) from wtcomValidRelationships as A, wtcomValidRelationships as B, wtcomValidRelationships as C, wtcomValidRelationships as D, wtcomValidRelationships as E where A.IDMember = @IDMember and charindex(convert(varchar(100),B.IDMember),A.RelationshipIDS) > 0 and charindex(convert(varchar(100),C.IDMember),B.RelationshipIDs) > 0 and charindex(convert(varchar(100),D.IDMember), C.RelationshipIDs) > 0 and charindex(convert(varchar(100),E.IDMember),D.RelationshipIDs) > 0 and PATINDEX('%'+@IDOther+'%',E.RelationshipIDs) > 0 end if (@Level = 6) begin select top 1 @Path = '' from wtcomValidRelationships end END
For a short time I have considered how to get the hierarchy in a self-linked table, incidentally I have found an article about ‘Recursive CTEs’ in ‘SQL Server Books Online’, where an example is very impressive and arouses me to find a way to get the ancestors or descendants of a given node in a self-linked (self-referenced) table.
And now I want to share this method to anyone who has the same problem to resolve or someone like me who has addiction in SQL.
First of all we have to create a table for the following functions and build some test data. The statemens look like:
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT) values(NEWID(), @rootoid, 'Business Application', 'group for all business applications')
declare @techoid uniqueidentifier set @techoid = NEWID()
insert into ST_CATEGORY(CATEGORYID, PARENTID, [NAME], COMMENT) values(NEWID(), @techoid, 'C#', 'C# tips') go
Now that the test table and data are prepared, we can get ancestors or descendants through the following four stored procedures of a given category.
CREATE PROCEDURE spGetAncestor @categoryID uniqueidentifier AS BEGIN -- find parents/ancestor WITH Ancestor( CATEGORYID ) AS ( SELECT PARENTID FROM ST_CATEGORY WHERE CATEGORYID = @categoryID UNION ALL SELECT PARENTID FROM Ancestor, ST_CATEGORY WHERE Ancestor.CATEGORYID = ST_CATEGORY.CATEGORYID ) SELECT * FROM Ancestor END GO
CREATE PROCEDURE spGetSelfAndAncestor @categoryID uniqueidentifier AS BEGIN -- find self and parents/ancestor WITH SelfAndAncestor( CATEGORYID ) AS ( SELECT CATEGORYID FROM ST_CATEGORY WHERE CATEGORYID = @categoryID UNION ALL SELECT PARENTID FROM SelfAndAncestor, ST_CATEGORY WHERE SelfAndAncestor.CATEGORYID = ST_CATEGORY.CATEGORYID ) SELECT * FROM SelfAndAncestor END GO
CREATE PROCEDURE spGetDescendant @categoryID uniqueidentifier AS BEGIN -- find children/descendant WITH Descendant( CATEGORYID ) AS ( SELECT CATEGORYID FROM ST_CATEGORY WHERE PARENTID = @categoryID UNION ALL SELECT ST_CATEGORY.CATEGORYID FROM Descendant, ST_CATEGORY WHERE Descendant.CATEGORYID = ST_CATEGORY.PARENTID ) SELECT * FROM Descendant END GO
CREATE PROCEDURE spGetSelfAndDescendant @categoryID uniqueidentifier AS BEGIN -- find self and children/descendant WITH SelfAndDescendant( CATEGORYID ) AS ( SELECT CATEGORYID FROM ST_CATEGORY WHERE CATEGORYID = @categoryID UNION ALL SELECT ST_CATEGORY.CATEGORYID FROM SelfAndDescendant, ST_CATEGORY WHERE SelfAndDescendant.CATEGORYID = ST_CATEGORY.PARENTID ) SELECT * FROM SelfAndDescendant END GO
Ok first I am still very new to SQL so I really do not understand quite a bit (SP's, Sub Query's,CTE, etc..). I also went through the SQLTeam article on trees and hierachies but only confused myself even more.
I will try my best to put all the information that I know of (in the shortest way that I know).....sorry I know this is long :(
SQLExpress2005 SQL Server Management Studio Express
Two Tables
PartMaster PRTNUM_01.......varchar(30) ////Part Number PMDES1_01.........varchar(50) ////Primary Description PMDES2_01.........varchar(50) ////Secondary Description
ProductStructure PARPRT_02........varchar(30) ////Top Level Part Number COMPRT_02......varchar(50) ////Component Part Number EFFDTE_02.........smalldatetime //// Effective Date QTYPER_02.........float ////// Quantity
I have gotten Close but no cigar with the following:
SELECT ps.PARPRT_02, ps.COMPRT_02, pm.PMDES1_01 + ' ' + pm.PMDES2_01 as Description, ISNULL(ps2.PARPRT_02, 'n.a.') AS [Sub Component], ISNULL(ps2.COMPRT_02, 'n.a.') AS [Part Number of Sub], ps.EFFDTE_02, ps.QTYPER_02 FROM ProductStructure ps INNER JOIN (SELECT COMPRT_02, MAX(EFFDTE_02) AS EFFDTE_02 FROM ProductStructure WHERE (PARPRT_02 = '4235') GROUP BY PARPRT_02, COMPRT_02) AS p3 ON ps.COMPRT_02 = p3.COMPRT_02 AND ps.EFFDTE_02 = p3.EFFDTE_02 Inner Join PartMaster AS pm On pm.PRTNUM_01 = ps.COMPRT_02 LEFT JOIN ProductStructure ps2 ON ps2.PARPRT_02 = ps.COMPRT_02 WHERE ps.PARPRT_02 = '4235' ORDER BY ps.PARPRT_02, ps.COMPRT_02
Line 1 - fine / Line 2 - fine / Line three (need to add description Part number of Sub)(need to keep original) / Line 4 and 5 same as line 3 / Line 6 and Line 7 should not show because the later date has a QTY of zero / Line 9 should not show because date is zero.
I once again appologize for the length and details on this , but this is has been driving me nutts. ANY help will be appreciated.
