how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
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] .....
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.
Hello all gurus out there.
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
All you help is appreciated.
Thanks much.
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
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 RelatedI need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies View RelatedIn Master tabel i have these date datas
2015-05-10
2015-05-11
2015-05-12
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON
A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND
A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN
WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
hi,
i have two tables with parent/child relationship - pipeline and pipelineStatus. the select statement like this:
SELECT *
FROM pipeline INNER JOIN
pipelineStatus ON pipeline.id = pipelineStatus.parentID
i got multiple records for each pipeline.id because of multiple records of pipelineStatus. Is it possible to get only one record for each pipeline.id with last record of pipelineStatus table?
(stored procedure ok)
thanks advance for answering my question.
Below is my sample data of my table named "Groups"
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
[Code] ....
Expected result:
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
[Code] ....
How to achieve this parent-child has the same table.
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.
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
INSERT INTO #T1 VALUES
( 200,3, '200-3-2-1' ),
( 100 , 14 , '100-14-1'),
( 3 , 2 , '3-2-1'),
( 2 , 1 , '2-1'),
( 14 , 1 , '14-1'),
( 1 , NULL , '1');
[Code] .....
how to find the orphaned value from the below parent/child hierarchy Table.
create table dbo.Hier(parent varchar(100), child varchar(100))
insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
I have a table called "College". In a table, I have to create a structure for multilevel parent-child relationship
For Example,
1) State has number of colleges, Number of colleges has Number of dept. , Number of dept. has no. of subjects, no. of subject has number of chapters and the hierarchy goes on.
Expected Output is,
College 1
Dept 1
subject 1
subject 2
subject 3
Dept 2
Dept 3
[Code] ....
I tried in so many ways, I do not know how to query in single table.
I have two table both say A and B.If i insert a record in A that record should be inserted in B.If i delete a record in A that record should be deleted from B.Is that possible.If yes please tell me.Thankyou in advance,vishnu
View 3 Replies View RelatedHi,
I have tables parent - PurchaseInvoices and child - PurchaseInvoiceDetails. I have trigger in PurchaseInvoiceDetails that would update stock qty based on Location code stored in parent table - PurchaseInvoices upon INSERT/UPDATE/DELETE.
I also set table RI to have cascade delete. I faced problem that, when I delete record in parent table, it would cascade delete child table. Trigger in child table fired and try to update stock qty. However, in this case, trigger unable to locate parent table record anymore.
How to overcome this? I can't move my stock update code to other place since I got to update stock if any changes happen to child table.
Please advice.
How do i find the child tables that belong to a parent table. Thanks.
I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?
1)1st method:-
Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.
Parent TAble
Id name
1 XYZ
Child table
id name parent id
1 abc 1
2 qwe 1
2)2nd method
table with id and parent ID. Top level element will have null value in table. eg
id name parent id
1 xyz
2 abc 1
3 qwe 2
4 adf 1
Retrieve data using recursive queries supported in SQL Express.
Which is the best solution to store parent child relationship???
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
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.
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.
Hi all,
I am sure someone must have run into this before. I have a couple of tables with a parent child relationship.
I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into.
However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship.
I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed.
However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction.
Is it possible to postpone trigger fire until after both parent and child table values have been inserted?
Thank you,
John
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 ...
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.
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
Hi all,
I got an error message 156, when I executed the following code:
////--SQLQueryParent&Child.sql---////////
Use newDB
GO
----Creating dbo.Person as a Parent Table----
CREATE TABLE dbo.Person
(PersonID int PRIMARY KEY NOT NULL,
FirstName varchar(25) NOT NULL,
LastName varchar(25) NOT NULL,
City varchar(25) NOT NULL,
State varchar(25) NOT NULL,
Phone varchar(25) NOT NULL)
INSERT dbo.Person (PersonID, FirstName, LastName, City, State, Phone)
SELECT 1, "George", "Washington", "Washington", "DC", "1-000-1234567"
UNION ALL
SELECT 2, "Abe", "Lincoln", "Chicago", "IL", "1-111-2223333"
UNION ALL
SELECT 3, "Thomas", "Jefferson", "Charlottesville", "VA", "1-222-4445555"
GO
----Creating dbo.Book as a Child table----
CREATE TABLE dbo.Book
(BookID int PRIMARY KEY NOT NULL,
BookTitle varchar(25) NOT NULL,
AuthorID int FOREIGN KEY NOT NULL)
INSERT dbo.Book (BookID, BookTitle, AuthorID)
SELECT 1, "How to Chop a Cherry Tree", 1
UNION ALL
SELECT 2, "Valley Forge Snow Angels", 1
UNION ALL
SELECT 3, "Marsha and ME", 1
UNION ALL
SELECT 4, "Summer Job Surveying Viginia", 1
UNION ALL
SELECT 5, "Log Chopping in Illinois", 2
UNION ALL
SELECT 6, "Registry of Visitors to the White House", 2
UNION ALL
SELECT 7, "My Favorite Inventions", 3
UNION ALL
SELECT 8, "More Favorite Inventions", 3
UNION ALL
SELECT 9, "Inventions for Which the World is Not Ready", 3
UNION ALL
SELECT 10, "The Path to the White House", 2
UNION ALL
SELECT 11, "Why I Do not Believe in Polls", 2
UNION ALL
SELECT 12, "Doing the Right Thing is Hard", 2
GO
---Try to obtain the LEFT OUTER JOIN Results for the Parent-Child Table
SELECT * FROM Person AS I LEFT OUTER JOIN Book ON I.ID=P.ID
GO
////---Results---//////
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
////////////////////////////////////////////////////
(1) Where did I do wrong and cause the Error Message 156?
(2) I try to get a Parent-Child table by using the LEFT OUTER JOIN via the following code statement:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'NOT'.
Can I get a Parent-Child table after the error 156 is resolved?
Please help and advise.
Thanks,
Scott Chang
Hello All,
I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables.
I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records.
When i looked at my sysoreignkeys table, it was empty(no rows).
Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records.
Thanks in advance
Regards,
Manoj Kumar
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).
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-)
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(); }
}
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!
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.