I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) that has a recursive
relationship, let me try to explain:
I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?
I tried the following two approaches:
1.) create a Table "Item" with Column "ItemID" as primary key, some
colums for the Data an Item can store and a Column "ParentItemID". I
set a foreign key for ParentItemID wich links to the primarykey
"ItemID" of the same table.
2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".
In both approaches when I try to delete an Item I get an Exception
saying that the DELETE command could not be executed because it
violates a COLUMN REFERENCE constraint. The goal behind these FK_PK
relations is is that when an Item gets deleted, all childItems should
automatically be deleted recursively.
How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using stored
procedures or something ?
I am using tables with recursive relationships extensively. For example the table tbComponent has a primary key called Co_ID and a foreign key called Co_Co_ID which references the field Co_ID. This allows a component to have unlimited child components, and each child component can have an unlimited number of tiers of children. I have a few question for which I have seen no documentation on.
1. How can I create a view or a SP that will return a component record and all of its children and children's children records down to the last/lowest child record?
2. I need to be able to do a search in this table. Example:
Table: tbComponent Columns: Co_ID Integer Co_Co_ID Integer Co_Name Text Co_Attribute Text Co_Category Text
Note: the data for Co_Category comes from a lookup table with also has a recursive relationship to itself where a category can have an unlimited number of tiers of children categories.
A typical group of records could be something like this:
Co_ID Co_Co_ID Co_Name Co_Attribute Co_Category
1 1 Car Blue Ford 2 1 Body Steel Parts 3 2 Door Front Parts 4 3 Invoice April 1 1999 Accounting
Ok, say there is over a million records in this table. Say I want to query this table and return all of the invoices for cars between March 1 1999 and May 1 1999. Say for example that there are less records where Co_Name has a value of Car then there are records with a value of Invoice, so logically I would set some kind of criteria to limit only invoices with where Co_Name = "Car". That's easy, I can return a result set of all the records Where Co_Name = "Car" and I can hold these in a view or a temp table. Now I need to query this View or temp table and see if it has any children records records Where Co_Name = "Invoice" . The problem is that the Invoice child could be a child record directly under the "Car" record, or 10 levels of children records down. The logic for this would be:
If Co_Name = "Car" Then Select * Where Co_Co_ID = 1, then take all of those record's values in Co_ID and run another statement Where Co_Co_ID = X and so on until there are no more children to search. If any of those records have a value of Invoice in Co_Name then return them.
Please don't give any speeches on Normal Relationship Database design suggesting a typical design using something like a Car table, a parts table, and a invoice table. I used the example above to demonstrate the dynamics of doing a search on a recursive relationship. I understand relationship theory and a recursive relationship is what we need to use in our situation.
Im really confused and need some help please from some of you clever people.
I know the basics of SQL but Ive no idea how to write a query to do this.
Basically, we have two tables, the first containing a list of people and the second containing two relationships to the person table. The first is the parent relationship and the second the child relationship. Each person may be related to one or more other people (e.g. father, son, brother, sister etc.). If you can imagine we have a few hundred people in the person going back a couple of hundred years.
Now Ive set the scene Im really trying to create a query that can navigate all the relationships from a set point (Person).
Ultimately I would like to (given a PersonID, navigate the full PERSON_ASSOCIATION table to find:
a. All people below the PersonID (if there are any) b. All people above the PersonID (if there are any)
For example, in the data below there is somebody called John Smith.
Going down the PERSON_ASSOCIATION table I would like to show:
John Smith has a brother called Andrew Jones who has a brother-in-law called Stephen Barnes
John Smith has a wife called Jane Wallace
John Smith has a daughter called Mary Joseph
What I am going round in circles on is the multiple relatationships, e.g. A is related to B who in turn is related to D who in turn is related to E therefore A is related to E.
PERSON ID NAME 1 John Smith 2 Andrew Jones 3 Jane Wallace 4 Steven Barnes 5 Mary Joseph 98 Me 99 Joanne
Can somebody please point me in the right direction by explaining how I can write some SQL that will navigate (and iterate through) all the PERSON and PERSON_ASSOCIATION tables.
Im sure there must be a straight forward way of doing this.
One final thing, if it helps, Im hoping to eventually end up with a stored procedure which I can basically pass in a PERSON_ID and it returns a list of all the relationships from that person.
Hi, I come back again. Can anyone help me to create table with many-to-many relationship. Here is my three tables tbl_Networks ( NID int identity(1,1) primary key, NetworkName nvarchar(256) )
tbl_Categories ( CID int identity(1,1) primary key, CateName nvarchar(256), NID int )
tbl_Sim ( SID int identity(1,1) primary key, NID int, CID int, NameOfSim nvarchar(256) ) My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them
I'm having trouble writing a query that references a join to a table with a recursive relationship in a SQL Server 2005 database.
I have a list of products in Table1. Each product belongs to a Category. Categories are stored in Table2 which has a self-referencing relationship that forms a hierarchical list of categories and sub-categories. Like so... ||-ID-||-Name-||-Parent-|| ||1 ||Cat1-----||Null-----|| ||2 ||Cat2-----||Null-----|| ||3 ||Cat3-----||1-------|| ||4 ||Cat4-----||1-------|| ||5 ||Cat5-----||2-------|| ||6 ||Cat6-----||2-------|| ||7 ||Cat7-----||3-------||
What I'm trying to do is write a sproc that will pull down all products that belong to a particular category or any of it's sub-categories.
I managed to write a sproc that could handle two levels of category by using union to join two queries together but I don't know how to handle an n-tier hierarchical structure.
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ======================================= -- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO ========================================= -- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
======================================== The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask: (1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables? Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period? (2) How can I set up the relationships among these 3 dbo Tables?
I'm having trouble creating a recursive function in T-SQL (SQL Server 2000).
I've got a table that has an ID column and a ParentID column. Each row can have a value in the ParentID column that references the ID column of another record - I'll call such rows "child records". I'll cal the row referenced by the ParentID the "parent record". Each child record can itself have another child record.
I need a function that will take an ID column value as a parameter, and walk up the chain of parent records until I get the first record in the series and return that record's ID value. I'll call that record the "UrParent record".
I'm trygin to create a recursive function called ufunc_ST_GetUrParentCertNum. In the function, there is of course a recursive call to itself - GetUrParentCertNum. However, when I try to run the CREATE FUNCTION script, I get the error: Server: Msg 195, Level 15, State 10, Procedure ufunc_ST_GetUrParentCertNum, Line 26 'ufunc_ST_GetUrParentCertNum' is not a recognized function name.
I tried the same thing with a Stored Procedure, and that worked fine. However, I really want this to work as a function.
Does anyone have advice on how I can achieve this? Thanks in advance.
Here is the error I am getting'role' table saved successfully'users' table- Unable to create relationship 'FK_users_role'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_users_role'. The conflict occurred in database 'raintranet', table 'role', column 'role_id'.table rolerole_id intname varchar 50table usersusers_id introle_id inTrying to get table.role_id to be related to role.role_idAny help would be appreciated
hello all, I am new to SQL server and dont have a clue on how to create a one-to-one relationship in sql server2000. Say, I have a table 'A' with a PK <customer_id> and another table 'B' with a PK <order_id>. Now to define a one-to-one relation between them how to do that? thanks in advance.
What is the best way to relate the name in dbo.server and name in dbo.ProcessorInfo so this is a one to many? Obviously I can't set the name column as a PK as I have more than 1 record of the same value in it.
How do I create a relationship between two tables when the two columns that should connect each table have different names?
i.e. Table Person Column Name < -- > Table Employee Column Person Name
The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.
Hi. I get this error when i try to create a relationship in a db diagram (sql 2005) "'tblActivedir' table saved successfully 'tblClient' table - Unable to create relationship 'FK_tblClient_tblActivedir1'. Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors."
What i have is 2 tables. 1 named client 1 named activedir
In the client table the columns i want to bind with activedirtable are FR1 and DC1 I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir. Fr1 has an fk relationship with activedir (pk is activedir' id) and DC1 exactly the same in another fk. So i want both columns to comunicate with activedir. If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1 I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both. Any help? Thanks.
Hi, I have metadata that stored my table structure and relationship. I would like to know is it possible to create table relationship programatically? Any sample?
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
Hi, I need help in creating a trigger before delete. The trigger should be in such a way that it should display a message, if there is any relationship with other table.
For example I have a table with employee details which have empid as primary key. I have another table with employee salary details where empid is foreign key. The trigger should check the relationship with these two tables. If I try to delete an emploeyee from employee details table and if there is a relationship of that employee with the salary table then the trigger should print a message. If there is no relationship then the trigger should perform the deletion. I want to create a trigger like this.
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
I have a problem related to create or show relationship digram in sql server 2005. Especially, I want to show relationship diagram that I established in sqlserver 2000 in sql server 2005 but I can't and I don't how to do it.
I am bring two fact tables and a Date Dim into my Power Pivot model. I try to create relationship where but Power Pivot just reverses them. When I create the relationship I get an info icon warning me it will be changed.Why can't I create a one to many relationship with fact table on many side and dimension on one side?Power Pivot will not let me mark my dimCalnder as a date table either. My date dim has surrogate key, year, month and monthlongname columns only...
i have a table where the feilds are 1.fromtable 2.fromfeild 3.fromcategory 4.totable 5.tofeild 6.tocategory. i have write a stored proceduer for creating 2 relationship between fromfeild & tofeild from the same value in from category & in tocategory. there r around 465 records in a table. so anyone can comeout for solution of this. hope soon i get a solution for this.
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
I need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score) Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
