Design Question - Parent Child Tables && Identity Columns
Apr 17, 2007
I don't know if this is the right forum but...
In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?
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
Sep 24, 2007
Report needs to display as follows
Parent
col1,col2,col3...
val1,val2,val3
expandable section
child1
colA,colB,colC
valA,valB,valC
child2
colA...
....
repeat
Am currently implementing this as nested list controls with textboxes due to the fact that the headers for the parent and child differ. (By header I mean the column labels)
1. is there a better way of doing this as I much prefer working with tables/matrix controls?
2. how can I stop the header from scrolling - this is possible with tab/mtx controls. The alternative is to repeat the headers all down the page which wastes space and looks messy
3. How can I apply 'interactive sorting' to the parent columns?
Thanks
View 1 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
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
Dec 17, 2007
From material I have read I should be able to reference columns in a datarelation by
Parent.ColumnName
Child.ColumnName
The code below is Ok until I use the Child reference
Dim tbl0102reltn As New DataRelation("tbl012tbl02", tbl01Cols, tbl02Cols)
dataset01.Relations.Add(tbl012tbl02reltn)
'=== Add the new Forecast Column to table01 (The display table)
Dim FcstTotVol As DataColumn = New DataColumn("FcstTotVol")
'declaring a column named Name
FcstTotVol.DataType = System.Type.GetType("System.Int16")
FcstTotVol.Expression = "Child(tbl012tbl02reltn).TotVol"
The last line throws an error
"cannot interpret token Child() at position 1"
If I replace the last Line as
FcstTotVol.Expression = "tbl0102reltn.Childtable.Columns(10)"
i receive the error
"The expression contains undefined function call tbl0102reltn.Childtable.Columns()."
I am unable to find away to feed the expression required for the new column.
Can anyone assit.
Thanks, Jim
View 1 Replies
View Related
Jul 20, 2005
Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.
View 2 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
Jun 15, 2006
Supose I have two records in a parent-child relationsuip (actually I have many more such records). Does SSIS offer any support ask for inserting one record into the parent table, the other into the child table, and updating the foreign key of the child to point to the parent?
TIA,
Barkingdog
P.S. I have to do just this as part of the datawarehouse test I'm running. Seems like a common task but I don't recall anything in SSIS addressing the issue.
View 5 Replies
View Related
Feb 29, 2008
My Question: Does anyone know of a decent way (i.e. I do not want to loop to insert each row and check the SCOPE_IDENTITY() field or anything like that) to copy parent/child rows to their same respective table besides using the method I have listed below (my manager does not really like the idea of the "PreviousID" field)? More details are listed below.
My Table Situation: I have a parent table and a child table. Both tables have an identity column as the primary key. The relationship between the tables is established using the parent table's primary key to the column in the child table that stores the relationship. The identity column in both tables is the only column that is unique in the tables.
Sample Data (made up and simplified for visualization purposes):
ParentTable - "Items"
ID ItemCategory Price
1,T-Shirt,$20
2,Blue Jeans,$50
3,T-Shirt,$40
ChildTable - "Components
ID ItemID Component
1,1,Fabric
2,1,ScreenPrinting
3,2,Fabric
4,2,Zipper
5,3,ThickFabric
6,3,ScreenPrinting
7,3,Elastic
My Need: I need to make a copy of the records (keeping the parent/child relationship intact) to the same table as the source records. For example, in my data example above, I may need to make a copy of all the "T-Shirt" items and their child records. As the parent records are copied, they will be assigned new keys since the primary key is an identity. Obviously, this new key needs to be used when creating the child records, but I need to somehow associate this new key to the new child records.
Possible Solution: I know this can be achieved by adding another column to the parent table to store the "PreviousID" (INT NULL). Using this new field, when I want to copy the "T-Shirt" items, I would insert the new records and store the ID of source records (i.e. the identity value of the row that was copioed would be stored in this "PreviousID" field). Once the parent record has been copied, I could then insert the child records, and I could join on the "PreviousID" field to get to the new ID to use for inserting the copies of the child records.
Thanks for reading this and for any help offered.
View 2 Replies
View Related
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
Mar 5, 2008
How do i find the child tables that belong to a parent table. Thanks.
View 10 Replies
View Related
Apr 8, 2015
I have a database with many tables. I would like to Delete all rows with practiceID=55 from all Parents tables and all corresponding rows from its child tables. Tables are linked with foreign key constraints (but there is no ON DELETE CASCADE).
How to write a generalized code for removing rows from both parent and child tables.
Query should pick parent table one by one and delete rows with practiceID=55 and all corresponding rows from its child tables
View 3 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
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
Sep 19, 2000
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim
Field 1 = ClaimTypeA_ID
Field 2 = ClaimTypeB_ID
Field 3 = ClaimTypeC_ID
Field 4 = ClaimTypeD_ID
Field 5 = ClaimTypeE_ID
Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
Any suggestions would be appreciated.
View 2 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
Aug 23, 2005
Normally when I have a "Many-toMany" or linkage table where the primary key consists of a foreign key from two different tables, I do not bother to make a separate identiy column instead.Does anyone see a reason why an identity column would be more or less desireable ? For exampleTable Person PK - PersonIDTable Car PK - CarIDTable PersonCar PK (PersonID, CarID)Or would it be better to make an Identity Column such as PersonCarID so then the table would look like the following:Table PersonCarPK - (PersonCarID)FK - PersonIDFK - CarIDCreate Unique Constraint on Person and CarIDAny feedback is appreciated
View 46 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
Apr 30, 2008
Hi all,
I am writing a portion of an app that is of intensely high online eCommerce usage. I have a question about identity columns and locking or not.
What I am doing is, I have two tables (normalized), one is OrderDemographics(firstname,lastname,ccum,etc) the other is OrderItems. I have the primary key of OrderDemographics as a column called 'ID' (an Identity Integer that is incrementing). In the OrderItems table, the 'OrderID' column is a foreign key to the OrderDemographics Primary Key column 'ID'.
What I have previously done is to insert the demographics into OrderDemographics, then do a 'select top 1 ID from OrderDemographics order by ID DESC' to get that last ID, since you can't tell what it is until you add another row....
The problem is, there's up to 20,000 users/sessions at once and there is a possiblity that in the fraction of a second it takes to select back that ID integer and use it for the initial OrderItems row, some other user might have clicked 'order' a fraction of a second after the first user and created another row in OrderDemographics, thus incrementing the ID column and throwing all the items that Customer #1 orders into Customer #2's order....
How do I lock a SQL table or lock the Application in .NET to handle this problem and keep it from occurring?
Thanks, appreciate it.
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
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
