SQL Server 2008 :: Compare Parent And Child Data?

Aug 4, 2015

DECLARE @ParentTable TABLE (ProductID BIGINT,ItemID BIGINT)
DECLARE @ChildParentTable TABLE (ParentID BIGINT,ProductID BIGINT,ItemID BIGINT)

--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively

INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237)
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)

--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively

INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578)
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)

--Here what I need is

--For product 701 there is one item missing ie 1237 which exists in its parent 101

--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102

--and extra item exists ie 8976 so my result table should lokk like this

/*
ParentProductID ProductIDItemIDIsExtra IsMissing
1017011237 01
1029015466 01
1029015489 01
1029016543 01
1029018976 10
*/

View 1 Replies


ADVERTISEMENT

Transact SQL :: Parent / Child Tables - Pivot Child Data To Parent Row

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

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

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

Transact SQL :: Need To Get Parent Child Data

Jun 24, 2015

I have a table with parent child relationship data.

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '06/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)
SELECT * FROM @DATA

1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data.  Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT

2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only

SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM
UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'
UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL

View 3 Replies View Related

Displaying Parent / Child / Grandchild Data

Nov 27, 2007

I am working on an ASP.NET site to display information about authors and books.  I'm working on the Author page right now and I'm having trouble getting it to display the data I want.
I'm trying to do everything with declarative controls and as little code-behind as possible.
I have a datasource on the main page that selects the Author data and provides this to a FormView.  Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList.
Inside the DataList I display some limited Book information.  I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles.  I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable.  Each datasource of possible several dozen is making its own call to the database and it's just too slow.
If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource.
Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page.
Is there a good way to do what I'm trying to do?
 
Thanks,
Graham

View 3 Replies View Related

Transact SQL :: Display Child Data Under Parent Row

Jun 25, 2015

I would like to display child row right after parent row with ORDER BY DataDate for below set of data.

DECLARE @DATA TABLE (DataUID INT PRIMARY KEY IDENTITY(1,1), DataId INT, DataName NVARCHAR(20), DataDate DATE, ParentDataName NVARCHAR(20))
INSERT INTO @DATA (DataId, DataName, DataDate, ParentDataName)
VALUES (1, 'child plan 1', '2015-06-09', 'Plan'), (1, 'child plan 2', '2015-06-08', 'Plan'),
(1, 'Design', '2015-06-01', NULL), (1, 'Implement', '2015-06-01', NULL),
(1, 'child Implement 1', '2015-06-09', 'Implement'), (1, 'child Implement 2', '2015-06-10', 'Implement'),
(1, 'Plan', '2015-06-01', NULL), (1, 'Operate', '2015-06-01', NULL)
select * from @DATA

1. as a example the child row 'child implement 1' & 'child implement 1' show correctly under parent 'Implement' with Order BY DataDate.

2. I'm looking for a SELECT query which should display 'child plan 1' & 'child plan 2' under parent 'Plan' with Order BY DataDate clause.

Below is the expected output I'm looking for,

View 6 Replies View Related

Insert Data Into Parent And Then Child Tables

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

Stored Procedure : How To Output Parent Child Etc Data

Mar 9, 2006

Q: How to Output Parent Child Data using 2, 3 or more related tables.


say you have 3 related tables where the Bold HouseID is the one to many relationship

Houses
HouseID
Name
add1


HouseOwer
AutoID
HouseID
Name
add1

HouseCorrespondent
AutoID
HouseID
Name
add1


Loop For each HouseID

step through the database
first get the ParentID from the Houses table
return that data

then get the related from the house owners table
return that data

then get the related correspondents from the House Correspondent table
return that data

end loop

ie return the date like below


Code:

HouseID | Name | add1
1 House1 abc Address
1 Mr abc Mr abc Ower Address
1 Mr abc Mr abc Correspondent Address

2 House2 def Address
2 Mr def Mr def Ower Address
2 Mr def Mr def Correspondent Address

3 House3 ghi Address
3 Mr ghi Mr ghi Ower Address



I hope ive explained well enough

View 5 Replies View Related

Transact SQL :: Parent Child Data With Different Order By Clause?

Jun 24, 2015

I'm asking my previous question in this new thread with more specific data and condition.I have below sample data,

DECLARE @DATA TABLE (U_ID INT, U_NM NVARCHAR(20), U_DT DATE, U_ORD INT, P_U_NM NVARCHAR(20) NULL)
INSERT INTO @DATA VALUES (1, 'Design', '06/15/2015', 2, NULL), (1, 'Plan', '07/01/2015', 1, NULL), (1, 'Cust Plan 1', '06/10/2015', 0, 'Plan'), (1, 'Cust Plan 2', '06/05/2015', 0, 'Plan'),
(2, 'Design', '06/25/2015', 2, NULL), (2, 'Plan', '06/20/2015', 1, NULL)

We have 2 different U_ID (1, 2) and I want a SELECT query to display,

1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2).
2. I want to display parent U_NM ORDER BY U_ORD
3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT
4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD

View 10 Replies View Related

Inserting Data Into Two Tables With Parent-child Relationship

Nov 13, 2006

I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?

If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.

The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.

View 1 Replies View Related

SQL 2012 :: Group By Parent With One Child And Multiple Child Information?

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

Transact SQL :: To Get Parent / Child / Grand Child Row On Various Order?

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

SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies View Related

SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table

Sep 21, 2015

I have three tables:

"PaymentsLog"
"DatePeriod"
"PaidOrders"

As per below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](

[Code] ....

Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?

View 3 Replies View Related

SQL Server 2014 :: Displaying Parent And Child Objects

May 15, 2014

Displaying a list of parent and child objects in a certain way, here's the code to create and populate an example dataset.

CREATE TABLE OrgStructure
(orgunitID int,
OrgUnitName varchar (100),
ParentID int)INSERT INTO OrgStructure
VALUES
(1,'OrgUnit 1', 0), (2, 'OrgUnit 2', 0), (3, 'OrgUnit 3', 0),

[Code] ....

The 3 OrgUnits with no parentID in one column (parentOU), then any OrgUnits who's parentID matches the orgunitid of the 3 OrgUnits in ParentOU in the next column (Child OU1), followed by any OrgUnits who's parentID matched the orgunitID of the OrgUnits in 'Child OU1' would be in the third column (Child OU2)

The dataset I'm working on contains ~700 rows and the OrgUnitNames can be anything, they aren't labelled like the example dataset with 1.1.1 and so on.

View 2 Replies View Related

SQL Server 2014 :: Select Parent And Child With HiearchyID

Sep 4, 2014

How to select parent and child with HierarchyID base on child attribute.

Here's my table :

CREATE TABLE #BOMTbl
(
ItemNo HIERARCHYID NOT NULL,
Lvl AS ItemNo.GetLevel() PERSISTED,
MatID VARCHAR(25) NULL,
CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)

[Code] ....

I wanna the result like this :

ItemNoItemIDLvlMatID
0x5AC0/1/1/2
0x5AD6/1/1/1/3MA-A
0x5ADA/1/1/2/3MA-A
0x5B40/1/2/2
0x5ADE/1/1/3/3MA-B
0x5B56/1/2/1/3MA-B
0x5B5A/1/2/2/3MA-B

View 4 Replies View Related

SQL Server 2012 :: Create XML With Parent And Child Element

Jul 8, 2015

I want to create an XML file with the below format.

office_tel and mobile are fields of a table.

<fields>
<office_tel>
<value>1234</value>
</office_tel>
<mobile>
<value>99999</value>
</mobile>
</fields>

View 2 Replies View Related

SQL Server 2012 :: Query To Select Parent Details From Child Table

Mar 3, 2015

I have a scenario,

We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.

Equipment table looks like :

Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177

There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.

Select query where i need to select the Code for all Parent_Id's.

View 8 Replies View Related

SQL Server 2012 :: Statement To Group Rows As Multiple Child Under Single Parent?

Sep 18, 2014

I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like

where london
where paris
where toronto

why us
why japan
why indonesia

I want to eliminate the repetitive question and group them as parent child items.

with cte as (
select cq.ConditionalQuestionID from ConditionalQuestions cq
inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y')
select distinct q.Question, a.Answer from QuestionAnswers qa
inner join Answers a on a.AnswerID = qa.AnswerID
inner join Questions q on q.QuestionID = qa.QuestionID
inner join cte c on c.ConditionalQuestionID = qa.QuestionID;

View 4 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related

Bcp In Child Before Parent

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

Insert Into Parent/child

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

Parent Child Transversing

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

Getting A Tree Using Parent-child

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

Parent Child Problem In Sql

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

Hierarchy – Parent And Child

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

Parent Child Tables

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

Execution Of Child Package From Parent Package In Sql Server 2005 Integration Services

May 21, 2007

Hi,

I created a package which passes some infornmations( through parameters) to its child package.

I need to do some processing in parent package based on execution status of child package.i.e.

if child fails then some operation and if child succeeds then other operation.

To determine the status of execution of child package I am using two differnt constraint ..one constraint is having value "Success" and other having value "Failure".

My problem is that when child packge is executed successfully the constraint with value = "Success" works properly but when child fails the constraint with value "Failure" does not work.

-Prashant

View 4 Replies View Related

Selecting Parent / Child Relationships

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

Insert Trigger For Parent/Child

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

Passing Variables In Dts From Parent Dts To Child Dts

Dec 24, 2004

Any one have any ideas or links to point me to ???

View 2 Replies View Related

Parent-Child View Without Using Cursors

Jul 26, 2007

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'


I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
Brian

View 12 Replies View Related

Using Same Table Having Parent And Child Relationship?

Mar 23, 2014

Below is my sample data of my table named "Groups"

Code:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;

Expected result:

Code:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;

some sample query to how to achieve this parent-child has the same table.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved