SQL Select query from 2 table Parent /child and retrieve 1 record for each parent

Question

SQL Select query from 2 table Parent /child and retrieve 1 record for each parent

PGRAY-542347

Old Hand

Points: 367
More actions
November 6, 2007 at 12:41 pm

#183608

I have looked for a solution to this problem for hours in google. I want to use sql select and retrieve last record entered in child database matching criteria of parent record.
Master (parent)
polno Name
1 Joe Blow
2 Sam Snead
3 tiger Woods
Transaction (child)
polno - Transdate - Balance -Type
1 - 10/01/2007 - 100.00 - Paym
1 - 10/01/2007 - 100.00 -Int
2 - 9/31/2007 - 200.00 - Paym
2 - 9/01/2007 - 190.00 - Int
3 - 8/31/2007 - 150.00 - Paym
I want to get from the above
1 10/01/2007 100.00 Paym
2 9/31/2007 200.00 Int
3 8/31/2007 150.00 Paym
Thanks, hopefully someone can come up with the Select statement to do this.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

PGRAY-542347 Old Hand Points: 367 More actions · Answer 1

I didn't preview the post and have since tried to fix the format of the question and how the data should look. I hope it is earier to read

Antares686 SSC Guru Points: 125444 More actions · Answer 2

1 - 10/01/2007 - 100.00 - Paym

1 - 10/01/2007 - 100.00 -Int

You state

1 - 10/01/2007 - 100.00 - Paym

is the output, what determines which Paym or Int is the one to display, also, what is Int?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

First - I'm assuming you meant 09/30/2007 because 9/31 is going to be hard to achieve.

The big question I have before heading into an example of how to do this is - how do you plan on handling "ties" (as in the case of Joe Blow). The personID and the date are a "tie" - do we return both records, or is there some logic you want as a "tie breaker"?

I'm assuming paym = payment, and int=interest, right?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

PGRAY-542347 Old Hand Points: 367 More actions · Answer 4

Thanks for the question, I know I haven't explained it very well.

Here goes,

The Transaction file is Indexed on Polnumb+dtos(transdate) descending

So I want the last record entered in my temporary cursor for further processing.

And I don't a 1 pass Select .... into cursor will work?

Any ideas

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

well then - keep expanding the definition. What do you plan on doing with the results? In other words - why do you need a cursor?

Cursor's tend to make database folks...curse. Can we talk you out of using it?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

PGRAY-542347 Old Hand Points: 367 More actions · Answer 6

Yes the 9/31/2007 should be 09/31/2007.

The Tie breaker would be the last record entered for that Polno.

When I set a relation from Master into Transaction file on Polno

and the Transaction file Index is set to Polno+dtos(Transdate) descending then I want that record only.

Is it possible or should I add another field with and incremented number added so I can use the MAX(...) function in my Select.

Thanks again

Antares686 SSC Guru Points: 125444 More actions · Answer 7

I would use Max on the date for the child table and then join to master, however if I need to understand how to determine precendence of the items to know which counts as the value you want to see. If Paym always supercedes Int them you can work like I state with some minor tweaking to make to account for choice.

PGRAY-542347 Old Hand Points: 367 More actions · Answer 8

Ignore the Paym, Int part of the record it has no significance, I just need the last record entered from the child table.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Well - if you don't use the extra field, you still CAN get just one record back per customer. It just might not be the same one every time. It probably would be best to throw in the extra field (whether it's an identity field that autoincrements, a datetime field with "create time", or anything else to break the tie).

At that point the MAX syntax should work. something like:

Select p.*

from policy_detail p inner join

(select polno, max(convert(char,transdate,112)+convert(char,tiebreaker)) as maxfield) from policy_detail group by polno) pd on p.polno=pd.polno and convert(char,p.transdate,112)+convert(char,p.tiebreaker) =pd.maxfield

Making the "maxfield" concept a single field which can then be indexed would speed this up quite a bit.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

PGRAY-542347 Old Hand Points: 367 More actions · Answer 10

PGRAY-542347

Old Hand

Points: 367

November 6, 2007 at 2:05 pm

#748858

Thanks, I will try it....now

PGRAY-542347 Old Hand Points: 367 More actions · Answer 11

You're a Genius, thank you so much works perfectly. I will analyze it and learn, Thanks for the Help oh mighty one.

Thanks

Paul

SQL Select query from 2 table Parent /child and retrieve 1 record for each parent

Cookies on SQLServerCentral