Insert Parent Child Records...
Apr 28, 2006
Hello,
We have a complex functionality of migrating data from a single record into multiple parent child tables.
To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.
Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.
As of now, we are using the following approach for achieving this functionality.
1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.
2. Use the recordset as the source to a foreachloop , using foreachADO enumerator
3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table
In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)
Thanks,
Satya
View 3 Replies
ADVERTISEMENT
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Oct 12, 2013
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Sql clearly doesn't like this
View 1 Replies
View Related
Dec 11, 2013
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
View 5 Replies
View Related
Feb 24, 2014
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS (
SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby
FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table.
2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
E.g.:
aaa
==>fff
==>ggg
bbb
==> aaa
==> hhh
eee
==> ccc
==> ddd
==> fff
View 5 Replies
View Related
Mar 13, 2008
Hi
i have a table named categorymaster
categoryid catname parentid
1 Boxing 0
2 Tennis 0
3 Basketball 0
4 MayWeather 1
5 Tyson 1
6 Clinton woods 1
7 RogerFederer 2
8 Micheal 3
9 Hingis 2
so if i give input say categoryid=1[This falls under main category-boxing]
i need to get result as
1 boxing [main category]
4 mayweather [sub category]
5 tyson [sub category]
6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson]
result should be as
1 boxing [main category]
5 tyson [sub category]
hope u can get my question
Thanks in advance
View 2 Replies
View Related
May 19, 2015
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date)
declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date)
insert @parent select 1, '1/1/2015', '12/31/2015'
insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
[Code] .....
View 6 Replies
View Related
Oct 29, 2006
Hi,I have a stored procedure that has to extract the child records forparticular parent records.The issue is that in some cases I do not want to extract all the childrecords only a certain number of them.Firstly I identify all the parent records that have the requird numberof child records and insert them into the result table.insert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select t1.BatchNumber,t1.EntryRecordID,t1.LN,t1.AdditionalQualCritPassedfrom(select BatchNumber,RecordType,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Temp) as t1inner join(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) = max(TotalNumbers)) as t2on t1.BatchNumber = t2.BatchNumberand t1.RecordType = t2.RecordTypeand t1.EntryRecordID = t2.EntryRecordID)then insert the remaining records into a temp table where the number ofrecords required does not equal the total number of child records, andthenloop through each record manipulating the ROWNUMBER to only selectthe number of child records needed.insert into @t_QualificationMismatchedAllocs([BatchNumber],[RecordType],[EntryRecordID],[AssignedNumbers],[TotalNumbers])(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) <max(TotalNumbers))SELECT @QualificationMismatched_RowCnt = 1SELECT @MaxQualificationMismatched = (select count(*) from@t_QualificationMismatchedAllocs)while @QualificationMismatched_RowCnt <= @MaxQualificationMismatchedbegin--## Get Prize Draw to extract numbers forselect @RecordType = RecordType,@EntryRecordID = EntryRecordID,@AssignedNumbers = AssignedNumbers,@TotalNumbers = TotalNumbersfrom @t_QualificationMismatchedAllocswhere QualMismatchedAllocsRowNum = @QualificationMismatched_RowCntSET ROWCOUNT @TotalNumbersinsert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select BatchNumber,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Tempwhere RecordType = @RecordTypeand EntryRecordID = @EntryRecordID)SET @QualificationMismatched_RowCnt =QualificationMismatched_RowCnt + 1SET ROWCOUNT 0endIs there a better methodology for doing this .....Is the use of a table variable here incorrect ?Should I be using a temporary table or indexed table if there are alarge number of parent records where the child records required doesnot match the total number of child records ?
View 2 Replies
View Related
Feb 25, 2008
hi,
i have two tables i want the identity value of the parent table to be inserted into the chile table
here is my code,but i don't know why it isn't working !
protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
thanks in advance :)
connection.Open(); commandd.ExecuteNonQuery(); }
}
View 2 Replies
View Related
May 24, 2013
I am trying to update a parent table with a summation of its child records. The child records are being deleted because the transaction has become invalid because payment was made with a bad check or there was a posting error. So a rollback of sorts is required.
Here are is the DDL for the tables and DML for the data:
Code:
DECLARE @t1 TABLE
(
[Year] int NOT NULL,
[Parcel] varchar(13) NOT NULL,
[InterestDateTime] datetime NULL,
[Principal] decimal(12, 2) NULL,
[Penalty] decimal(12, 2) NULL,
[Code] ....
I tried to use a Merge statement with an ON MATCH for each TransType, but it complained that I could not have multiple update statements. OK. So I tried a MERGE with single update statement with a case and it complained that I was updating the same parent multiple times, which I was and want to! So, I tried the following update statement and it still does not work, though no error message.
Code:
update t1 set
t1.Principal = t1.Principal + (case when t2.TransType = 'R' then t2.Payment else 0 end),
t1.Penalty = t1.Penalty + (case when t2.TransType = 'P' then t2.Payment else 0 end),
t1.Interest = t1.Interest + (case when t2.TransType = 'I' then t2.Payment else 0 end)
from @t1 t1
inner join @t2 t2 on t2.YEAR = t1.YEAR and t2.Parcel = t1.Parcel
I am expecting the following after the update:
Code:
Select * from @t1
201200000018092013-03-14 00:00:00.000 211.15 10.00 3.14
201100000018092013-03-14 00:00:00.000 206.12 10.00 18.20
201000000018092013-03-14 00:00:00.000 219.41 10.00 35.37
200900000018092013-03-14 00:00:00.000 0.00 0.00 0.00
2012000001808X2013-03-14 00:00:00.000 9.65 0.00 0.06
2011000001808X2013-03-14 00:00:00.000 378.70 10.00 32.73
2010000001808X2013-03-14 00:00:00.000 0.00 0.00 0.00
2009000001808X2013-03-14 00:00:00.000 341.96 3.00 142.74
All I am getting are the original values.
View 14 Replies
View Related
Mar 19, 2007
I have a set of parent/child records that need to be exported to a space delimited Flat File. Each parent record must be followed by 3 child records, each on their own line with different format.
I have a prototype using the Derived Column component that concatinates the various fields of each record into one "wide" text column. This fools SSIS to think that each row has the same format. Then I merge them together using an artificial sort id. But this seems overly tedious and very brittle.
What would be the best approach to writing these records out? I'm hoping there is a better more maintainable method.
Thanks,
Jon
View 4 Replies
View Related
May 9, 2006
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger.
Thanks, James
View 1 Replies
View Related
Aug 14, 2007
Hello,
I am wondering if there is a way to insert one parent record with multi child records in one transaction? I am using dataset to update my database. I want to use transaction so if one record insert fails all the transctions rollback.
Thanks
Your Input would be greatly appricated.
View 3 Replies
View Related
Aug 7, 2015
I have a single complex query.
SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>
View 2 Replies
View Related
Sep 24, 2007
Thanks for your time,
How to calculate & save a Parent status [qcStatus varchar(30)] and Alert [alertFlag bit] in dbo.a1_qcParent
based on comparison of its Child records in dbo.a3_qcItems2Fix columns [itemComplete bit] and [alertFlag bit]
Where a1_qcParent[a1_id] = a3_qcItems2Fix[a1_ID]
- Parent CLOSED: if all children [itemComplete] are True
- Parent OPEN: if any child [itemComplete] is False
- Parent ALERT: True if any child row [alertFlag bit] is True
Using sql_Datasource in webpage, but more comfortable in sql... After-Trigger?
Can Parent columns have calculated formula referencing the child table? Please help.
View 1 Replies
View Related
Oct 16, 2015
I need to create a trigger to meet following conditions.
When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.
When parent record is deleted child records needs to be inactivated in table.
View 12 Replies
View Related
Oct 18, 2006
Hi
I need to read a file and write parent/child relationship rows. I cannot seem to fugure out how I can generate keys that I an use for the relationship.
I looked at using a variable, but have no joy - I cannot instantiate the variable and it errors - notsure wy.
I also looked to see if I can write to a DB table that maintains key id, but not sure how to do that.
I alo thought of setting up the parent table key to be auto-increment,but canot see how I can read this so that I can us it in the child row inserts.
Help will be really appriciated
Thanks in advance.
View 3 Replies
View Related
Nov 16, 2006
hello -
i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.
For example, I have a table Users. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDepartment - it has a foreign key for the user id and a foreign key to the department being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.
I looked at multi-cast, but I don't know/think that this will work for me.
does anyone know of a good example or article like this?
thanks
- will
View 8 Replies
View Related
Oct 31, 2007
Folks,Using NorthWind as Example: Parent Table derived from: Categories. I added a new Column E-Mail and Selecting rows where Category Id <=3. Here is my Data.
Category ID
Category Name
Category E-mail
1
Beverages
Beverages.com
2
Condiments
Condiments.com
3
Confections
Child Table derived from: Products. I am Selecting rows where Category Id <=3. Here is my Sample Data.
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottles
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jars
3
Chocolade
10 pkgs.
3
Gumbär Gummibärchen
100 - 250 g bags
3
Maxilaku
24 - 50 g pkgs.
3
Scottish Longbreads
10 boxes x 8 pieces
3
Sir Rodney's Scones
24 pkgs. x 4 pieces
3
Tarte au sucre
48 piesI would like to read 1st Category Id, Category E-Mail from Categories Table (ie. Category Id = 1), find that in Products Table. If match, extract matching records for that Category from Both Tables (Categories.CategoryID, Products.ProductName, Products.QuantityPerUnit) and e-mail them based on E-Mail Address from Parent (Categories ) Table. If no E-Mail Address is listed, do not create output file. In this instance Category Id = 3.Basically I want to select 1st record from Parent Table (Here is Category) and search for all matching Products in Products Table. And Create an E-mail and sending just those matching records. Repeat the same process for remaining rows from Categories Table. I am expecting my E-Mail Output like this: For Category Id: 1
Category ID
Product Name
Quantity Per Unit
1
Chang
24 - 12 oz bottles
1
Côte de Blaye
12 - 75 cl bottles
1
Ipoh Coffee
16 - 500 g tins
1
Outback Lager
24 - 355 ml bottlesFor Category Id: 2
Category ID
Product Name
Quantity Per Unit
2
Aniseed Syrup
12 - 550 ml bottles
2
Chef Anton's Gumbo Mix
36 boxes
2
Louisiana Hot Spiced Okra
24 - 8 oz jars
2
Northwoods Cranberry Sauce
12 - 12 oz jarsI am not extracting the Data for any user Interface (ie. Grid View/Form View Etc). I will just create a Command Button in an ASP.NET 2.0 form to extract Data. My Tables are in SQL 2005. I was thinking to read the Category records in a Data Reader and within the While Loop, call a SP to retrieve the matching records from Products Table. If matching records found, call System SP_Mail to send the E-mail. The drawback with that for every category records (Within While Loop) I need to call my SP to get Products Data. Will be OVERKILL? Ideally I would like extract my records with one call to a SP. Is there any way I can run a while loop inside the SP and extract Child Data based on Parent Record? Any Help or sample URL, Tutorial Page will be appreciated. Thanks
View 3 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
Jul 25, 2014
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
View 1 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
May 17, 2006
Hi...how can I write a dynamic sql statement to insert x number of child records? Thx in advance
View 4 Replies
View Related
Oct 17, 2007
I am developing an application in vb.net 2005 using SQL Server 2000.
In this I have two tables SessionMaster and SessionChild.
Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type,
Fields of session child - SessionChildId, SessionMastId, UserName, Comment.
SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields.
Please how to write trigger to insert record into both tables at a time.
View 2 Replies
View Related
May 19, 2004
Huh?
I've got good RI data...BUT..a developer loaded the tables in alpha table order....
Such that the child loaded BEFORE the parent....
Huh?
Got a test being set up now to mess with the child file to add a key that doesn't exist in the parent...
But Why is this allowed?
In DB2 you can specify
LOAD DATA REPLACE NO CHECK....
On the load card...you then need to run a check after to verify the data...
Is that what's going on? Is there such a utility in SQL Server to run a check post load?
I'm confused....
Any comments appreciated.
Thanks
Brett
8-)
View 2 Replies
View Related
Jul 16, 2005
I have a parent/child relationship in a relational database broken out like this:
Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int
The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this:
select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories)
Each bottom-level category has a count attached to it.
The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts).
My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything).
Thanks!
View 3 Replies
View Related
Sep 27, 2013
Here is the table - Company with fields:
CompanyID, ParentCompanyID (both integers)
Given a CompanyID - I want to get all the children for the Company.
I did similar procedures but somehow, could not get this to work.
View 4 Replies
View Related
Jul 1, 2007
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
ParentID ChildID
-------------------------
10 8
8 6
8 7
7 2
7 5
5 1
5 2
6 2
child of parent if input 10
8
7
6
2
2
5
1
(2) can be neglected
input 7
2
5
1
(2) can be neglected
input 8
7
2
5
(2) can be neglected
Plz help we to write the sql statements
Thanks
View 2 Replies
View Related
Aug 2, 2007
I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form:
Id | parent_id
---+-----------
1 | 0
2 | 0
3 | 0
4 | 1
5 | 1
6 | 4
7 | 4
8 | 7
9 | 7
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id.
Is this a good relational database design ? kindly suggest.
Id | parent_id | root_id
---+-----------+---------
1 | 0 |
2 | 0 |
3 | 0 |
4 | 1 | 1
5 | 1 | 1
6 | 4 | 1
7 | 4 | 1
8 | 7 | 1
9 | 7 | 1
10 | 2 | 2
11 | 2 | 2
12 | 10 | 2
13 | 10 | 2
Rgds
Vijay
View 3 Replies
View Related
Jul 20, 2005
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
View 1 Replies
View Related
Jun 28, 2004
Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result.
I think I thought this out correctly:
I have 2 tables
category
relationship
tbl category
cat_id //auto int
cat_name // varchar
relationship
r_id // auto int
parent_id // int
child_id // int
both the parent_id and child_id are associated with the cat_id
in my category table I could have
1cars // this is parent
2 audi
3 bmw
4 chevy
Table data example
r_id parent_id child_id
****************************
1 1 15
2 1 16
3 1 17
4 2 55
5 2 56
etc...
I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = #
I can do it manaully like this
select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1
what is the best way to loop through all the parent ids to find child category?
Could this be done in a stored procedure?
thanks in advance.
View 3 Replies
View Related
Dec 24, 2004
Any one have any ideas or links to point me to ???
View 2 Replies
View Related
