BorlandTalk.com

Ron Tuijnman · Guest

Hi,

Thought it would be a piece of cake, but somehow I'm keeping doing something
wrong.

I have 3 tables: A- a master table with the dectription of a proces.
B- a detailtable describing the progress of the
proces in numeric steps
C- a detailtable with the explanation of the
progress integer in table B

What I want is only the instances with the highest progres-number of that
certain proces (table A)

E.g.
Table A: Sales data (curstomer, product, etc.)
Table B: The progress of the sale 1, 2, 3 etc. (seven steps in total)
Table C: (1=offered - 2=accepted - 3=delivered etc.)

From a certain sale I want only the record from B with the highest number so
that I can see where I stand in this particular sale.

Tried different SQL-statements with MAX( ) involved, but somehow it did not
give me a unique record for every sale:
Sale 1 Accpeted
Sale 2 Offered
Sale 3 Accepted
Sale 4 Accepted
Sale 5 Delivered
etc. etc.

Must be a tiny mistake I'm making, so can anybody give me some pseudocde how
to do this??

Thanx!
Ron

Oscar Santiesteban Jr. · Guest

select * from tableA join tableB on tableA.field1 = tableB.field1
where tableB.step = ( select max(step) from tableB
where tableB.field1 = tableA.field1 )

This was done in my head, but I think it should work.
This assumes field1 is the key that links tableA to tableB.

Oscar...

"Ron Tuijnman" <Ron (AT) nowhere (DOT) com> wrote