Hierarchical (tree) Data Structure Where A Node Can Have Multiple Parents
May 9, 2008
Hi all!
I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options:
Example 1
id parent_id name
-----------------------------------
1 0 Level 1 Parent A
2 0 Level 1 Parent B
3 1,2 Level 2 Child
Example 2
id parent_id name
-----------------------------------
1 0 Level 1 Parent A
2 0 Level 1 Parent B
3 1 Level 2 Child
3 2 Level 2 Child
Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table?
Thanks in advance,
View 4 Replies
ADVERTISEMENT
Aug 3, 2013
CREATE TABLE #Source
(
Id int identity(1,1)
,categoryint
,Leaf_Node_code varchar(10) --
,Level1_Name varchar(20)
,Level2_Name varchar(20)
[Code] ....
Here category 1 has 3 levels ,
category 2 has 4 levels ,
category 3 has 5 levels ,
Below is the target table, here Leaf_Node_code should populate to only for leaf nodes for each category .. Need to populate Node_id with hierarchical data
I am unable frame a sql query to handle different levels , in future #Source may have more levels .
How to handle multiple hierarchy levels .. here only leaf node should have Leaf_Node_code
CREATE TABLE TARGET_TABLE
(
ID INT IDENTITY(1,1) primary key
,Node_id HIERARCHYID
,category int
,Parent_id int references TARGET_TABLE(id)
,Leaf_Node_code varchar(10)
,Namevarchar(20)
)
Here is the expected output:
IDcategoryParent_idLeaf_Node_codeName Node_id
11NULLNULLWorld
211NULLAsia
312101India
42NULLNULLa
524NULLaa
625NULLaaa
726102aaaa
83NULLNULLb
938NULLbb
1039NULLbbb
11310NULLbbbb
12311103bbbb
View 1 Replies
View Related
Jul 20, 2005
Hi,I have a tree structure which is maintained through the use of a pathenumerated column:CREATE TABLE items (item_id NUMERIC NOT NULL,path VARCHAR2(64) NOT NULL);The path is is a colon separated list of ids of the nodes of the tree.So, for example, in this structure:0 -> 1 -> 2 -> 3 -> 4item id 4 would have a path of '0:1:2:3' (0 is the root of allitems, and does not actually exist). Notice that the path does notinclude the item's own id.I would like to select all of the items in a given item's path:SELECT id, path FROM items WHERE id IN (PATH_TO_IDS(path));or maybe:SELECT id, path FROM items WHERE PATH_EQUALS(id, path));or maybe something else altogether. This should return:ITEM_ID PATH------- -------1 02 0:13 0:1:24 0:1:2:3
View 1 Replies
View Related
Apr 6, 2015
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
*/
[code]....
View 2 Replies
View Related
Apr 7, 2007
Hi all
I have a Table with Following structure ( a Tree Structure )
PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105
The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?
PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100
Any help greatly would be appreciated.
Kind Regards.
View 2 Replies
View Related
Jan 28, 2005
I use hieararchical tree with parent id.
I want to select all records that are children of one record.
Oracle has Connect by statment, but sql server doesn't provide this.
Does enyone know a sql script for this problem?
View 1 Replies
View Related
Feb 27, 2008
I am trying to modify a piece of SQL where I want to get the date difference from todays date and the datestamp on the latest record on dbo.activitym1 table AC1 ..which matches the following criteria..WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number)
PS1.number is the master record ID
But this doesnt seem to work ..the best result i've been able to achieve is all records from the dbo.activitym1 table that match the where clause and i only want the latest by date
heres the code
any help would be most appriciatedSelect PS1.number, PS1.brief_description," +
" DateDiff(day, PS1.open_time, Getdate()) as 'Days_Open', PS1.company," +" (select DateDiff(day, AC1.datestamp, Getdate())" +
" From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.activitym1 as AC1" +" WHERE (AC1.Type = 'Assignment' or AC1.Type = 'Reassignment') and PS1.number = AC1.number) as 'Days_Since_Last_Assigned'," +
" CONVERT(VARCHAR(10), PS1.open_time, 103) as 'Date_logged'" +
" From " + ConfigurationManager.AppSettings["DATABASE_NAME"] + ".dbo.probsummarym1 as PS1" +
"where (PS1.assignment = 'PROD - UK CENTRAL CPH COGNOS SUPPORT' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH CONS HEALTH' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH PUBLICATIONS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH TERRITORY PLANNER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL CPH VIEWPLUS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL CHEMICAL PIONEER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY PROFILES' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL COMPANY SEARCH' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL GENERIC PLNG & ANLYSER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE NPF' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE PATENTS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL LIFECYCLE RDF' OR " +
" PS1.assignment = 'PROD - UK CENTRAL EDITORIAL MIDAS GENERICS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING ANALOGUE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING GENERICS MARKET PROGNOSIS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING MARKET PROGNOSIS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING OTC REVIEW' OR " +
" PS1.assignment = 'PROD - UK CENTRAL FORECASTING THERAPY FORECASTER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CHEM INTPACK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CLASSFN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CORP' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB CUSU' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB DSG' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB GPIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB INTPRD' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB KNOWLEDGELINK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB LICENSING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB LPIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB MARK SEG' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB OTHER' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB PRICES FACTORS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB STANDARDS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL GLOB WHO' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT ACCOUNTS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT ADMINS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT EXEC' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA CODING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA CPMS' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA PROD CTRL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT HPA QUALITY CONTROL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT INTL FIELDWORK' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT IT SOL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PANEL CTRL' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA CODIN' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA PROCESSING' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PATIENT DATA QRY & SUPPORT' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PRODUCT REFERENCE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT PSO' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT QUALITY ASSURANCE' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT RESEARCH HOSPITAL GROUP' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SIT WARD' OR " +
" PS1.assignment = 'PROD - UK CENTRAL SITTINGBOURNE' )" +
" and UPPER(PS1.Status) <> 'CLOSED'
View 2 Replies
View Related
May 30, 2008
hi,
How to get 2 level tree node in the sql server 2005. We have table with data.On load we populate the tree in Asp.net.
Regards,
Vinayak Panchal
View 2 Replies
View Related
Nov 15, 2006
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
View 1 Replies
View Related
May 12, 2015
I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.
The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.
Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).
I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2
--***** Table Definition With Insert Into to provide some basic data ****
IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,
[Code] ....
How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?
View 7 Replies
View Related
Sep 12, 2005
Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika
View 3 Replies
View Related
May 2, 2007
After having built a decision tree model to predict a boolean output attribute using 64-bit SQL Server 2005 (build 9.0.3054), we have observed that predictions for some cases are being done at non-leaf nodes in the tree.
Specifically, after executing a prediction join which returns:
- CaseTable.CaseID
- MiningModel.OutputAttribute
- PredictProbability(MiningModel.OutputAttribute)
- PredictNodeId(MiningModel.OutputAttribute)
and comparing the values of PredictNodeID(MiningModel.OutputAttribute) with the mining model content column [NODE_UNIQUE_NAME] to determine the actual "rule" used to make the case-level prediction.
We have observed that for a subset of cases, predictions are being made at nodes in the tree that are not leaf nodes. Specifically, predictions are being made at a node that is 3 levels deep. The leaf nodes below this inner-tree node are 2 levels further down the tree.
Also supporting the fact that that predictions are being made at this non-leaf node is that the PredictProbability corresponds exactly with the output attribute distribution at this non-leaf node.
In this particular application, we would have obtained better results if the predictions were made at the leaf-nodes.
A few questions:
1. Why are predictions with decision trees made at non-leaf nodes?
2. Is there a way to "force" predictions to occur at leaf nodes via DMX?
Thanks in advance for any information or advice.
- Paul
View 1 Replies
View Related
Jan 30, 2004
Hi,
I am trying to figure out if this is possible in Oracle or Mysql
Lets say I have 3 tables such that C could have either A or B as its parent.
A
{ id, name}
B
{id, name}
C
{other_id, comment}
Now other_id could be either A.id or B.id. What I want to be able to do is to define a foreign key constraint of the type:
CONSTRAINT FK_C FOREIGN KEY (other_id) REFERENCES A(id) ON DELETE CASCADE,
CONSTRAINT FK_C FOREIGN KEY (other_id) REFERENCES B(id) ON DELETE CASCADE
such that deleting A.id automatically deletes C.other_id where A.id = C.otherid and same for B.id
Ofcourse i am not able to do this. Is there any way that this can be done in Oracle and Mysql?
Thanks a lot,
Priyanka
View 2 Replies
View Related
Sep 11, 2007
Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy?? Thank you very much for any ideas!!
M.
View 5 Replies
View Related
Jul 20, 2005
I have a user assigned multiple roles and a role can be inherited frommultiple parents (see below). How do I answer such questions as "Howmany roles does the user belongs to?"I answered the above questions by using .NET but I think it can bemore efficient by using just SQL. I would appreciate if you can giveme an answer.Thank you.CREATE TABLE [dbo].[tb_User] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,[Password] nvarchar(99) NOT NULL,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Role] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_User_Role] ([UserId] [int] NOT NULL ,[RoleId] [int] NOT NULL,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED([UserId],[RoleId]) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Parent_Role] ([RoleId] [int] NOT NULL ,[ParentRoleId] [int] NOT NULL ,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED([RoleId],[ParentRoleId]) ON [PRIMARY]GO
View 1 Replies
View Related
Aug 23, 2004
I have the following table
Memberid int
submemberid int
example
MemberId SubMemberId
1 2
2 3
1 4
3 5
2 6
7 8
Each member may supervise more than one member under him (whom I call submember)
and then each sub member may also supervise more than one member under him
I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so
on
I do not know how to do this here is my trial:
ALTER Procedure dbo.MemberReports;1
@MemberMaskId nvarchar(8)
As
SET NOCOUNT ON
Begin
Declare @Exists Int -- Return Value
-- Select * from MembersTree where MemberId = @memId or MemberId = subMemberId
SELECT
MembersTree.memberid,
MembersTree.submemberid,
MembersTree1.memberid
FROM
MembersTree
INNER JOIN
MembersTree MembersTree1
ON MembersTree.Submemberid
= MembersTree1.memberid
WHERE
MembersTree.MemberId = @memId
If @@rowcount = 0 -- No Record returned
Select @Exists = 0
Else
Select @Exists = 1
Return @Exists
End
Please help,
Thanks
View 2 Replies
View Related
Nov 6, 2005
hello i have a table with four fields : name_id, name, tree_id, level withthese data:1 name1 001 12 name2 002 13 name3 003 14 name4 001001 25 name5 001002 26 name6 001003 27 name7 001001001 3tree:1-- 4-7-5-62-3-and procedure which add nodes to tree looks tree.add(idparent,idchild,name)how to write select which returns idparent,idchild and name then i can addnodes in loop reading datareader ?
View 4 Replies
View Related
Sep 22, 2006
Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?
Example:
...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...
This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.
How can i import such a node in SqlServer 2005?
Thank you in advance for any help
Chris
View 8 Replies
View Related
Dec 11, 2006
hello friends!
Can any body guide me regarding Tree/hierarchy Multi-Referential table structure.
What i have is only for known tree structure i.e upto 3 or 4 level but if i waanna to find nth level hoe shud i design my table structure.
T.I.A
View 3 Replies
View Related
Jul 30, 2007
I’m trying to create a modified catalog tree out from two tables in sql
The catalog is created from two tables.
Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog
MainCatalogId, ItemGroupId, ItemId
Example data:
(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826
The catalog structure is in table 2. Here are the connections between the different
ItemGroupId, ParentId
Example data:
ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL
To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:
ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667
I have tried many ways, but I’m not getting the result I want.
Hope this was understandable, and that someone has an idea if this is manageable
Thanks :)
View 1 Replies
View Related
Dec 21, 2007
Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality
If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure
i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes
Country Department
+ America + Computers
---Florida -- HP
+ SothAfrica + Sales
---Capetown __Bikes
How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .
Thanks In advance
Jacks
View 3 Replies
View Related
Jun 3, 2015
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
View 3 Replies
View Related
Aug 18, 2015
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).
View 6 Replies
View Related
Apr 11, 2007
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
p_id, p_attribute_1, p_attribute_2, p_attribute_3, c1_attribute_a, c1_attribute_b, c2_attribute_a, c2_attribute_b, ...., cn_attribute_a, cn_attribute_b
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.
View 4 Replies
View Related
May 1, 2008
I'm using SSIS to import seven flat files (each containing a different record type) into a staging database. This part was easy.
Now I need to export the records from all seven tables into a single flat file structured in a nested hierarchy using common keys. (This format is required by the vendor for loading data into a new system).
I could use some ideas on the data transformations needed to combine all seven record types into an hierarchical record set which can then be written to my Flat File Destination. I'm currently looking at an article on SLQIS.com ("Handling Different Row Types In The Same File") which seems close to what I need, but they are importing (ref: www.sqlis.com/54.aspx ). I'm not sure if I should just reverse this for export or use something different. Any comments are appreciated.
Diagram of Record Hierarchy
typeA (parent key, ...)
typeB1 (parent key, childSet key, date, ...)
typeB2 (parent key, childSet key, ...)
typeC (parent key, childSet key, ...)
typeD (parent key, childSet key, ...)
typeE1 (parent key, childSet key, date, ...)
typeE2 (parent key, childSet key, ...)
The record types B1 through E2 form a complete set. Each set has it's own unique child-set key. There may be one or more sets for each typeA record (although it's possible that typeE records don't exist in the most recent set).
View 3 Replies
View Related
Jun 9, 2006
Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...
Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);
Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)
What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.
Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?
View 8 Replies
View Related
Nov 10, 2006
My problem, I have the aspx, .cs and ascx files but I don t have a project or solution that came with the files (since that was done by front page). So I need to add all those files in a solution or a project so that I can maintain the site that was created by front page using Visula Studio.
Do you know how i Can put all those files in a new project or new solution.
2/Also, when we modify the the pages, how can i put them beck in the site, will I have to compile the files again, or not. thank you.
Thank you
View 1 Replies
View Related
Sep 28, 2007
Hi,
I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node.
what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.
thanks,
RK
View 1 Replies
View Related
May 1, 2006
How can I create a function that returns hierarchical data from a table with this structure:
- CategoryID
- CategoryName
- CategoryFather
I want to bring the result set like this...
CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2
How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.
Thanks.
View 5 Replies
View Related
Apr 17, 2008
Hello
I have a problem that I am hoping somebody can help me with!
I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-
Newspapers
National Newspapers
Daily Express
Express Publications
Express Supplements
Daily Mail
Mail Publications
Mail Supplements
Mirror
So my table would look like below:-
1 Newspapers Null
2 National Newspapers 1
3 Daily Express 2
4 Express Publications 3
5 Express Supplements 3
and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.
NewsPapers National Newspapers Daily Express Express Publications
NewsPapers National Newspapers Daily Express Express Supplements
Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.
Any help would be greatly received!
Thanks
Rich
View 5 Replies
View Related
Feb 17, 2006
hi folks,
I have to import hierarchical text files like:
32;country;city;postalcode;street
21;name;firstname;salutation;title;age;nickname
21;name;firstname;salutation;title;age;nickname
...
additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?
Iam looking forward for any helping idea.
rgrds,
matze
View 11 Replies
View Related
Nov 30, 2013
I have two table. Department is hierarchical table.
Department
--- id (int primary key)
--- name (varchar)
--- parent (int)
Users
--- Id
--- name
--- department_id
This query return all data from departments. But i cannot understand how get data from users
SELECT t1.name AS lvl1, t2.name as lvl2, t3.name as lvl3
FROM Department AS t1
LEFT JOIN Department AS t2 ON t2.parent = t1.id
LEFT JOIN Department AS t3 ON t3.parent = t2.id
View 1 Replies
View Related
Oct 12, 2007
Hi,
in my application, the data is in hierarchical format. there is a tree with set of nodes having parent child relationships. this data can be stored either through adjacency or nested set model approach. this is fine. but the issue here is that each child node inherits the properties of its parent node, parent's parent node and so on until the root node. lets say root node has two attributes A1 and A2 and they are stored in two columns in a table. but its child nodes inherits this data from its parent and it has its own extra attributes. so should I copy parent's data for the child node as two additional columns? the problem is that there are around 15 levels in the tree and the attribute list grows from top to bottom in the tree. lets say I need to find all the attributes for a leaf node in the tree (both direct and inherited), if I am not storing the inherited attributes for each node, then I need to walk-up the tree and find all the inherited attributes. there are around 30K nodes and each node has around ten attributes. xml is not option because of large volumes of data and auditing and reporting on individual nodes. what is the best way to store this type of data? my current approach is to have an attribute table having nodeid as a foreign key and only store the direct and NOT the inherited attributes of the node in the table, but this means to find all the attributes for the node, I need to gather the attributes of all the parents until the root node. I can't see any easy way out for this.
View 4 Replies
View Related
