473,320 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selecting TOP X child records for a parent record

Hi,

I have a stored procedure that has to extract the child records for
particular parent records.

The issue is that in some cases I do not want to extract all the child
records only a certain number of them.

Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select t1.BatchNumber,
t1.EntryRecordID,
t1.LN,
t1.AdditionalQualCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumbers)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordID = t2.EntryRecordID)

then insert the remaining records into a temp table where the number of
records required does not equal the total number of child records, and
thenloop through each record manipulating the ROWNUMBER to only select
the number of child records needed.

insert into @t_QualificationMismatchedAllocs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbers))

SELECT @QualificationMismatched_RowCnt = 1

SELECT @MaxQualificationMismatched = (select count(*) from
@t_QualificationMismatchedAllocs)

while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumbers = AssignedNumbers,
@TotalNumbers = TotalNumbers
from @t_QualificationMismatchedAllocs
where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationMismatched_RowCnt =

QualificationMismatched_RowCnt + 1
SET ROWCOUNT 0
end

Is there a better methodology for doing this .....

Is the use of a table variable here incorrect ?

Should I be using a temporary table or indexed table if there are a
large number of parent records where the child records required does
not match the total number of child records ?

Oct 29 '06 #1
2 3298
Looping is always to be avoided if possible. What version of SQL
Server are you running? In the (unlikely) event it is 2005, I think
it should be possible to eliminate the looping, as TOP accepts a
variable in 2005, rather than requiring a constant.

Even with other releases I think I see a way to eliminate the loop.
Assuming that QualMismatchedAllocsRowNum in table
t_AuditQualifiedNumberExtractDetails_Temp is an identity column or
other unique value, I think this can replace the entire loop process.

INSERT INTO t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
SELECT BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
FROM t_AuditQualifiedNumberExtractDetails_Temp as A
WHERE TotalNumbers <=
(select count(*)
from t_AuditQualifiedNumberExtractDetails_Temp as B
where A.BatchNumber = B.BatchNumber
and A.RecordType = B.RecordType
and A.EntryRecordID = B.EntryRecordID
and A.QualMismatchedAllocsRowNum <=
B.QualMismatchedAllocsRowNum)

That would require a good index on the columns (BatchNumber,
RecordType, EntryRecordID).

I take it that QualMismatchedAllocsRowNum in table
@t_QualificationMismatchedAllocs is an identity column? Are the
values always starting at 1? My concern is looping through a lot of
numbers that might not exist in the table.

Looking at the tests that split the data into two categories:
>having count(*) = max(TotalNumbers)) as t2
having count(*) <max(TotalNumbers))
brings up a question. What sort of row count do we get from each of
the three relationships?

count(*) = max(TotalNumbers)
count(*) < max(TotalNumbers) -- If this is significant, see below.
count(*) max(TotalNumbers)

The reason I ask is that I do not see a difference in the final
results between count(*) < max(TotalNumbers) and when they are
equal. In both those cases all the rows are used. The looping
process appears to me to only be required when there are MORE rows
(count(*) max(TotalNumbers)) than should be inserted. If I have not
missed something, and if a significant part of the processing is for
count(*) < max(TotalNumbers), then the first test could be modified
to:

having count(*) <= max(TotalNumbers)) as t2

And the one used to generate the exceptions used in the loop changed
to:

having count(*) max(TotalNumbers))

Which would cut down on the looping (or the alternate joining
proposed) process.

Roy Harvey
Beacon Falls, CT

On 28 Oct 2006 23:26:51 -0700, "Catch_22" <ca********@yahoo.co.uk>
wrote:
>Hi,

I have a stored procedure that has to extract the child records for
particular parent records.

The issue is that in some cases I do not want to extract all the child
records only a certain number of them.

Firstly I identify all the parent records that have the requird number
of child records and insert them into the result table.

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select t1.BatchNumber,
t1.EntryRecordID,
t1.LN,
t1.AdditionalQualCritPassed
from
(select BatchNumber,
RecordType,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp) as t1
inner join
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) = max(TotalNumbers)) as t2
on t1.BatchNumber = t2.BatchNumber
and t1.RecordType = t2.RecordType
and t1.EntryRecordID = t2.EntryRecordID)

then insert the remaining records into a temp table where the number of
records required does not equal the total number of child records, and
thenloop through each record manipulating the ROWNUMBER to only select
the number of child records needed.

insert into @t_QualificationMismatchedAllocs
([BatchNumber],
[RecordType],
[EntryRecordID],
[AssignedNumbers],
[TotalNumbers])
(select BatchNumber,
RecordType,
EntryRecordID,
Count(*) as AssignedNumbers,
max(TotalNumbers) as TotalNumbers
from t_AuditQualifiedNumberExtractDetails_Temp
group by BatchNumber, RecordType, EntryRecordID
having count(*) <max(TotalNumbers))

SELECT @QualificationMismatched_RowCnt = 1

SELECT @MaxQualificationMismatched = (select count(*) from
@t_QualificationMismatchedAllocs)

while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
begin
--## Get Prize Draw to extract numbers for
select @RecordType = RecordType,
@EntryRecordID = EntryRecordID,
@AssignedNumbers = AssignedNumbers,
@TotalNumbers = TotalNumbers
from @t_QualificationMismatchedAllocs
where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt

SET ROWCOUNT @TotalNumbers

insert into t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
(select BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
from t_AuditQualifiedNumberExtractDetails_Temp
where RecordType = @RecordType
and EntryRecordID = @EntryRecordID)

SET @QualificationMismatched_RowCnt =

QualificationMismatched_RowCnt + 1
SET ROWCOUNT 0
end

Is there a better methodology for doing this .....

Is the use of a table variable here incorrect ?

Should I be using a temporary table or indexed table if there are a
large number of parent records where the child records required does
not match the total number of child records ?
Oct 29 '06 #2

Hopefully you saw my response to your other message.

Roy Harvey
Beacon Falls, CT
Oct 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Stuart McGraw | last post by:
I haven't been able to figure this out and would appreciate some help... I have two tables, both with autonumber primary keys, and linked in a conventional master-child relationship. I've...
1
by: Johann Blake | last post by:
I have a dataset that contains a parent table and a child table. A DataRelation exists between the two. I was under the impression from reading the VS docs that when I filled the parent table, the...
1
by: Aaron Smith | last post by:
I have a parent table that has one child table. The parent has a single field (ID, AutoIncrement, Seed 0, Step -1 in the DataSet, Seed 1, step 1, in DataSource). The child is linked to this ID...
5
by: PAUL | last post by:
Hello, I have 2 tables with a relationship set up in the dataset with vb ..net. I add a new record to the parent table then edit an existing child record to have the new parent ID. However when I...
2
by: Thelma Lubkin | last post by:
My ColorSet building form/subform now works beautifully, thanks to the help that I've gotten from people in this group. The working form displays the parent ColorSet record with the child records...
2
by: Bob | last post by:
I got three related datagrid views one parent and two children of the same. The two child tables contain many thousands of records and some of the contents are bitmap files in a sql server...
1
by: Hexman | last post by:
Hello All, What I'm trying to do is update a child record using a parent-child relation. I want to find out if it is faster than than doing multiple selects. Anyways, I've created a dataset...
2
by: Swinky | last post by:
I hope someone can help...I feel like I'm walking in the dark without a flashlight (I'm NOT a programmer but have been called to task to do some work in Access that is above my head). I have...
8
by: Rick | last post by:
VS 2005 I' m setting up a parent/child datagridviews in a form. I am doing a lot of this by hand coding in order to get the feel of things. I want a change in the parent table to trigger a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.