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
ADVERTISEMENT
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
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
Apr 20, 2015
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link: [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
View 15 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
Nov 8, 2005
Greetings,I just wanna know if anyone can tell me how to get all user definedtables in parent-then-child manner. I mean all the parents should belisted first and then childs.I dont think there is any direct way to do this, but i am not able toform any sort of query to achieve this.Any help will be greatly appreciated.TIA
View 7 Replies
View Related
Apr 20, 2015
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:
[URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.
View 1 Replies
View Related
Nov 4, 2015
I have a table as below
ItemID ParentID
6 NULL
7 NULL
8 7
11 7
12 8
Here ParentID null means it does not have the parent its the master. I want result in below format.
ItemID ParentID
6 NULL
--No parent no child
7 NULL
--No parent but has child
8 7
--7 is the parent of 8
12 8
--8 is the parent
11 7
--7 is the parent
Basically it should be in parent id then child id after that child's childID , In a recursive way.
View 5 Replies
View Related
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
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
Oct 31, 2015
I have a Recursive CTE for TFS database which gives me below results:
Parent (User Story) level 0
-------------.Task(Dev) and ask(QA) --
level 1
BUG level 2
---------------Task (Dev) and Task(QA)
level2
So, My ParentID column keeps two ParentId one for User story which keeps the child tasks and child bugs and another for Bug Child tasks
I need to update this results and want to see all of the tasks under User story so the result I want is:
User Story:
Task(dev and QA)
Task (Dev and QA ) but its should know that these tasks are the bug tasks.
View 10 Replies
View Related
Nov 19, 2015
I have a tblActivity table :
ActivityID ActivityName
-----------------------------
1 A1
2 A2
3 A3
4 A4
5 A5
6 A6
7 A7
8 A8
9 A9
10 A10
and related it in tblParentActivity table with parent child relationship as:
ParentActivityID ChildActivityID
-------------------------------------
1 2
1 3
2 4
2 5
3 6
4 7
5 7
6 8
7 9
8 9
9 10
I want to write a SQL query which will make it in tracks depend on how many childActivity in one parentActivity like
ActivityID TrackGroup
-------------------------
1 1
2 1
4 1
7 1
9 1
10 1
[code]....
View 8 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
Oct 22, 2015
I need to write recursive query to find child of a parent until the last leaf. Below is my code.
;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)
When I execute this code. It is returning me millions of rows. Whereas i have only 20 rows in a table max 40 rows it should return.
View 7 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
May 12, 2015
In 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.
View 10 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
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
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
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
Dec 24, 2004
Any one have any ideas or links to point me to ???
View 2 Replies
View Related
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
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
Apr 4, 2008
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.
View 5 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
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
