Get One Row From Detail/child Table

Jul 20, 2005

Hi,

-- ddl
/*
create table #tmp (col1 int);
insert into #tmp values(1);
insert into #tmp values(2);
insert into #tmp values(3);

create table #tmpChild (col1 int, fkCol int, Num int);
insert into #tmpChild values(1,1,3);
insert into #tmpChild values(2,1,2);
insert into #tmpChild values(3,2,1);
*/

-- get parent and child data (outer join)
select *
from #tmp t Left Outer JOIN #tmpChild tC
ON t.col1 = tC.fkCol

-- resultset
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
1 2 1 2
2 3 2 1
3 NULL NULL NULL

(4 row(s) affected)
*/

-- desired resultset
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
-- eleminate next row, want only Max(Num) row from the child tbl with
same FK (parentID)
-- 1 2 1 2
2 3 2 1
3 NULL NULL NULL
*/

In other words, desired results is as follows:
/*
/col1 col1 fkCol Num
----------- ----------- ----------- -----------
1 1 1 3
2 3 2 1
3 NULL NULL NULL
*/

How to accomplish this task? ENV: MS SQL Server 2000

TIA

View 1 Replies


ADVERTISEMENT

Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table

Nov 1, 2007

I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
 
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' 
 
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return.  Same goes for the last_name field.   Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..

View 7 Replies View Related

Selecting Detail Based On A Sum Of The Detail Lines

Sep 14, 2007

I am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.

I was trying Group by and Having but this doesn't seem to do what I need.

Being relatively new to Reporting services, any nudge in the right direction would be useful.

View 4 Replies View Related

Count From Detail Table

Feb 20, 2008

HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC

View 2 Replies View Related

Charting Table Detail And Sum

Dec 13, 2005

I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)

View 3 Replies View Related

Tranact SQL : Get Total Of Detail Table

Jan 28, 1999

Hi,

I have a master and a detail table as defined :

Master : tmpOrder

Column_name Type
------------------------------ ------------------------------
OrderID int
Date DateTime

Detail : tmpOrderItem

Column_name Type
------------------------------ ------------------------------
OrderID int
Seq int
Qty float
Amount float

How can I get the master fields and sum of qty and amount in one query. I need the following result set

OrderID Date TotalQty TotalAmount
-------------- ---------- ---------- --------------

Thank you in advance
Allex

View 1 Replies View Related

Master/Detail Table Insertion.

Sep 20, 2007

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

View 6 Replies View Related

Main And Detail Table Amount Calculations

Oct 31, 2006

Hi all,

I have a 2 main tables in my system. One is main table has ticket information location and so on. And detail table which hold all actions on that record with multiple money fields (its around 5 fields). it goes trough some crazy calculations on the detail table and get 5 results for each ticket. And this calculation is done everytime the ticket is looked at.
There is around 10 detail records per 1 main record. and the main table is around 7 mil. records

Now i got 2 options. I can create a trigger that does the calculation and update the main table show the results from there. This is going to affect all updates deletes and inserts.

OR

I can leave as it is so only when they view the ticket it does the calculation. Dont ask me how often they view it i dont have a clue.( if there is any suggestion how to get the count i could try to do it. )
:confused:
Suggestions

(My attitude is going towards the trigger way but 100 percent sure)

View 4 Replies View Related

Retrieving Only 50 Chars From Detail Table Of Comments

Jan 25, 2012

I have a detail table of comments. I need to grab the fist 50 characters from the comment field from this table. The kick is that I need to get the comments from all of the records in the detail table, as the comments might be less than 50 characters, so i need to pull the comments from the other comment records that apply to the master table record.

right now i have the comments in a join to the master table, but this is causing a few records to be returned....

I guess what i am looking for is a way to grab the comment value from the first record of the detail table.

View 1 Replies View Related

In Master And Detail Table Fetch Record.

Jun 24, 2008

Dear Sir
Thank for your reply but our requirements are not this
I am fully explain my requirements

In Master table have 5 columns; In Master Table stored all records.

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0
Ad630Log10
Data810Summary0
Data820View0
Data830&View0
Data840Row10
Ad630Mbl20



In second Table store those records who are selected and stored in 2nd table.
NameAppidFunctionCodeFunNameSubFunCode
Data810Summary0
Data820View0
Data830&View0
Ad630Mbl20


Our requirements we use one query,
In query fetch total 5 rows. and output show like this

NameAppidFunctionCodeFunNameSubFunCode
Ad630Manual0N
Ad630Log10N
Data810Summary0Y
Data820View0Y
Data830&View0Y
Data840Row0N
Ad630Mbl20Y


Please immediate reply me,
I am waiting your reply.
Thanks Asit Sinha

View 3 Replies View Related

Conditional Suppression Of Table Detail Row And If/then Statement

Oct 26, 2007



Two questions:


1) I am unable to get a detail row of my table to suppress. I am using:


=Iif(IsNothing(Fields!Address2.Value), True, False)


2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements:


=Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08)



Tx
Les

View 7 Replies View Related

Insert Master/Detail Table Using Store Procedure!!!

Oct 1, 2007

now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...

View 1 Replies View Related

Transform Header And Detail Flat File Into One Table

Sep 13, 2007

I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.


The file looks something like this:

FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60

So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?

View 1 Replies View Related

SQL Server 2012 :: Combining Master And Detail Records In New Table

Sep 4, 2014

CREATE TABLE DHS(CUSTOMERNBR VARCHAR(20), CONTRACT VARCHAR(20), SUBCONTRACT VARCHAR(20) , STARTDATE DATETIME, ENDDATE DATETIME, EFLAG VARCHAR(20), HFLAG VARCHAR(20))

The data which will be going into this table is from two table which have a 1 to many relationship:

Here is the 1 side and data:

CREATE TABLE CUSTOMERS(
CUSTOMERNBR VARCHAR(20),
CONTRACT VARCHAR(20),
SUBCONTRACT VARCHAR(20),
STARTDATE DATETIME,
ENDDATE DATETIME DEFAULT '12/31/2099')

[Code] ....

Here is the Many side and data:

CREATE TABLE FLAGS(CUSTOMERNBR VARCHAR(20), FLAGCODE VARCHAR(20), STARTDATE DATETIME, ENDDATE DATETIME DEFAULT '12/31/2099')
INSERT INTO FLAGS(CUSTOMERNBR, FLAGCODE, STARTDATE, ENDDATE) VALUES('10001000101', 'H', '02/01/2014', '03/31/2014')

[Code] ....

The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:

CUSTOMERNBRCONTRACTSUBCONTRACTSTARTDATEENDDATEEFLAGHFLAG
10001000101A910400801/01/201401/31/2014
10001000101A910400802/01/201403/21/2014H
10001000101A910400804/01/201404/30/2014
10001000101A910400805/01/201405/31/2014H
10001000101A910400806/01/201412/31/2099E
10001000102A555500101/01/201403/31/2014E
10001000102A555500104/01/201404/30/2014EH
10001000102A555500105/01/201406/30/2014E
10001000102A555500107/01/201412/31/2099
10001000103A666600401/01/201410/01/2014

View 1 Replies View Related

SQL Server 2008 :: Insert Trigger On Master Detail Table

Mar 14, 2015

I have two tables. Order table and order detail table.

What I want to do is send notification to a service when an order is created. And I want to include both header and detail of the order.

I can't get working with on insert trigger . How to go around with trigger ?

View 1 Replies View Related

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

Jan 15, 2008

I have detail table like

date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8


Result View
=======

Item_id A B O T Total


001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18

where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.

Smartsys

View 4 Replies View Related

SQL Master/Detail (Master Query Based On Detail Values)

Mar 25, 2008



Hello,

I'm new to SQL and need help with a query. Not sure if this is the right place.

I have 2 tables, one MASTER and one DETAIL.

The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.

I want to return a populated MASTER table with entries based on the DETAIL.value.

SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3

This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.

Hope this makes sense.

How can I do this?

GrkEngineer

View 9 Replies View Related

Integration Services :: Insert Data Into Header And Detail Table From XML Through SSIS Package

Jun 2, 2015

I need to insert data into Header & Detail table. As shown in the below xml,

RecordID is identity-column and incremented by 1, after new record is saved into Header table. Need to assign the same recordID for the detail also.

Expecting output should be like as shown below:

How can we accomplish this requirement.

View 8 Replies View Related

Automatically Adding Records To Child Table When Record Added To Parent Table

Aug 19, 2006

In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks

View 1 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I 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)

View 5 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

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 Related

Transact SQL :: Retrieve Currently Created Date From Master Table To Load Into Parent And Child Table

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

HOW TO: Delete All Records From A Table Where A Child Record From Another Table Does Not Exist.

Mar 17, 2008

I need to delete all records in the TBL_PCL_LENS_DATA table that do not have a corresponding record in the TBL_VERIFICATION table.

Primary Table: TBL_PCL_LENS_DATA
PK: Serial Number
PK: ProcessedDateTime

Child Table: TBL_VERIFICATION
PK: Serial Number

Thanks,
Sean

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

Child,chid Of Child ,child Of Child Of Child

Oct 22, 2007

 
 
............child  |parent|.............a1     |a     | .............a2     |a     |.............a11   |a1    |.............b1     |b     |.............b11   |b1    |.............b111 |b1    |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select   querry?

View 3 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

How To Update Child Table Using The PK And A Seq #

Jun 17, 2002

Good morning,
I have two tables PARENTTBL which is a parent table and CHILDTBL which is a
Child table . For each insert in the Parent table i want to insert values in the child Table but (when inserting in child table) i want the Field FILED2 which is a varchar one will receive the value of the PKEY int cancatenated with a sequnetial value for each new PKEY
FILED2 = Rtrim(LTreim(Str(PKEY))+ Sequential # . which is a
Sequential # by PKEY value .

Exemple : If i insert
100,A,B in PARENTTBL (100 is a PK value)
i want to insert in CHILDTBL the following data :
125,100-1
126,100-2
300,100-3
452,100-4
The first column is the Child PK and the second column is the FILED2
I want to do this in one insert instead of insert then Update.

I don't find the equivalent of Number(*) of SYBASE or ROWNUM of Oracle in order to do This .

Thinks to help me

View 3 Replies View Related

Retrieve Values From Child Table

Feb 2, 2005

Hello there,

I need to get the last value (status) from a child table. I try to simplify the problem with the following example.

Create Table Users
(
UserId int,
Lastname nvarchar(50)
)

Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)

Create Table Status
(
StatusId int
Status nvarchar(50)
)

A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:

Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2

Will not return the wanted records because all authorized Users have been in this status.

I hope you understand the problem and can help me out.

Thx in advance.

Etinuz

View 3 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

Parent/Child Table Replication

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

Get Child Value From A Single Table Structure

Jun 13, 2008

Hi

I have a single table which holds a hierarchical a structure. I would like to get all the child ID's from one Stored Proc call. For example I give it an ID in the structure and I would like to give me all the child records that exist to the lowest level for each category found

My table structure is
[SvcID] [varchar](30) NOT NULL,
[Ser_SvcID] [varchar](30) NULL,
[SvcDesc] [varchar](100) NOT NULL

I know where the root of the hierarchy is because Ser_SvcID will be null. Im not sure how to do this in one stored procedure call

Hope the above makes sense

Many thanks in advance

View 2 Replies View Related

Deletion Of Parent And Child Table

Feb 28, 2008

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

View 3 Replies View Related

Parent And Child Records From Same Table

Mar 13, 2008

Hi

i have a table named categorymaster

categoryid catname parentid
1 Boxing 0
2 Tennis 0
3 Basketball 0
4 MayWeather 1
5 Tyson 1
6 Clinton woods 1
7 RogerFederer 2
8 Micheal 3
9 Hingis 2

so if i give input say categoryid=1[This falls under main category-boxing]
i need to get result as
1 boxing [main category]
4 mayweather [sub category]
5 tyson [sub category]
6 clinton woods [sub category]

if i give categoryid=5[Note:Tyson]
result should be as
1 boxing [main category]
5 tyson [sub category]

hope u can get my question
Thanks in advance

View 2 Replies View Related







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