hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP SELECT * INTO Emp FROM ( SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b */
Hi all, Im designing a product menu like the one seen here. I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappy Diagram!
I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table CaseID, CaseNumber, CaseName
CaseChild Table CaseID, FK to CaseInfo ChildPartyID, FK to PartyID in Party table ProgramClosureDate
Party Table ID, PartyID, Firstname, LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate FROM CaseInfo ci JOIN CaseChild cc ON ci.CaseID = cc.CaseID JOIN Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help
I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.
In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk
The external agency can only use a delimited file looking like
where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.
How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent **20-parent - 9-parent --250-child1 --210-child2 --240-child3 aggregation of 12-parent only aggregation of 20-parent only aggregation of 9 with children
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) THEN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code: select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) END ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code: SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
I would like to know how to create a single level hierachy structure in SQL. Example, I have a single parent record in table A that may later on spawn one or child record in table B that relates back to the parent. It only needs a single level. that means, one to many.
table1 has 3 fields a , b and c. field "a" is a primary key. table2 has 2 fields x and y. Fields x and y are nothing but the value of "a". Also, y is the child of x. Therefore, x and y can never have same value. It means value of "a" either be child or parent. But there is possibility that parent has no child.
Now, i wanted to write Select/Insert query for parent, b , c and child.
Hi, I'm having a major brain-failure moment here. Using T-SQL I want to be able to get all of the leaf nodes (e.g. nodes at the furthest end of a tree from the root) in a hierarchical relationship where the table structure is such NodeID|ParentID|NodeName. Basically if I had the following tree structure: Root Child1 Grandchild1 Child2 Grandchild2
I want to get all of the Grandchild nodes. Number of levels will vary and I haven't got any kind of HasChilds column. I know this is possible because I remember having done it on a course years ago but I can't for the life of me figure it out on this sunny Friday afternoon. I know it's going to involve either recursion, a while loop or cursors but my mind is currently jelly. Can anyone help?
HI, I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed for this i need a table and procedure to complete family tree Thanks @mbi
Consider the following SQL query: SELECT ENAME,SAL FROM EMP,ASG,PAY,PROJ WHERE EMP.ENO=ASG.ENO AND PAY.TITLE=EMP.TITLE AND ASG.PNO=PROJ.PNO AND ASG.DUR=48 AND BUDGET>200000 Give the possible operator trees:right-deep,left-deep and bush Tank you very much!
i'm writing a app in c# and have to store Trees in a Database.
I'm working with Datasets for the exchange between the DB and the App.
The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.
But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:
Not Possible:
create Table tree ( Id varchar Not null, ParentId varchar Not null, Constraint pk1 Primary Key (Id), Constraint fk1 Foreign Key (ParentId) references tree(Id) On Update Cascade On delete CAscade )
Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action
Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?
I'm desperate, reli need to work out how too do this.....
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.
Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:
NetworkId Full Name Sec Class Desc Sec Type Desc SecValue tte Test Scenario Accounting Budget Center 142- ?
SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description, d.Security_Type_Description, a.Security_Value FROM Company_Hierarchy_Security a JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
------------------
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description, f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description, i.Team_Description from Company_Hierarchy_Security a. Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID Inner JOIN Company f on a.Security_Value = f.Company_ID Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5)) Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID Inner JOIN Team i on a.Security_Value = i.Team_ID
Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks
Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy
I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?
Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)
my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.
wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?
i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......
i have tried 3 different solutions all coming to a crash at some point, if ANYONE can help me out i would appreciate it, i know the logic will be recursive, i just dont know how to implement
Hi..I have a table register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent. suppose table is like this. username parentid downline B A left C A right D B left E B right.... I have to also determine the level in the tree....please help...
I just started having this problem. When I log into SQL2000 enterprise manager and click on my database, the main directory tree shows up. When I click on the Database tree to display all of the different databases on the SQL server, the text 'No Items' shows up. I can not get to any databases, but I am connected to the SQL server. Does anyone have a solution for this problem?????
I have tried re-inatalling and all of the service packs.
Okie, this one has me a little stumped so bear with me an I will explain as best I can....
I have a family tree database...
The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).
Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.
If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.
I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.
One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks GO DROP TABLE LI_Users GO DECLARE @d1DATETIME , @d2DATETIME , @d3DATETIME , @d4DATETIME , @d5DATETIME , @d6DATETIME , @d7DATETIME , @d8DATETIME
ALTER TABLE LI_Userlinks ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)
SELECT @d3 = GetDate()
INSERT INTO LI_Users ( uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5 FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z2 CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z3 CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z4 CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z5
SELECT @d4 = GetDate()
INSERT INTO LI_UserLinks ( uid_from, uid_to) SELECT uid, 100 * uid + n0 + 10 * n1 FROM LI_Users CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 WHERE LI_Users.uid BETWEEN 0 AND 99 AND uid != 100 * uid + n0 + 10 * n1
SELECT @d5 = GetDate()
INSERT INTO LI_UserLinks ( uid_from, uid_to) SELECT uid, 100 * uid + n0 + 10 * n1 FROM LI_Users CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0 CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z1 WHERE LI_Users.uid BETWEEN 100 AND 9999
SELECT @d6 = GetDate()
SELECT u.uid, r1.uid_to, r2.uid_to FROM LI_Users AS u INNER JOIN LI_UserLinks AS r1 ON (r1.uid_from = u.uid) INNER JOIN LI_UserLinks AS r2 ON (r2.uid_from = r1.uid_to) WHERE 1 = u.uid
SELECT @d7 = GetDate()
SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to) FROM LI_Users AS u INNER JOIN LI_UserLinks AS r1 ON (r1.uid_from = u.uid) INNER JOIN LI_UserLinks AS r2 ON (r2.uid_from = r1.uid_to)
