Hello,

I have what I hope is a simple sql (DB2/AS400) question.
Assume 2 files exist. One is a transaction file with two fields, the
transaction ID and the transaction name. File two is keyed to file 1
through the transaction id and contains a transaction date. Since
transactions can be processed more than once, the transaction id can appear
in the second file any number of times, but at least once.

Now for my question. I need to be able to select all records in file 1 while
joining to file 2 and selecting the most recent date. Am i making any
sense?

In other words, I need to see the transaction id, name, and most recent date
from file 2, but a simple join will return multiple instances of the
transaction ID where I want to only see each ID once?

Any help appreciated.

"cg_news" <cag_ng@-sbc-global.ten> wrote in message
news:PL%Mc.2554$AY5.265@newssvr21.news.prodigy.com ...[color=blue]
> Hello,
>
> I have what I hope is a simple sql (DB2/AS400) question.
> Assume 2 files exist. One is a transaction file with two fields, the
> transaction ID and the transaction name. File two is keyed to file 1
> through the transaction id and contains a transaction date. Since
> transactions can be processed more than once, the transaction id can[/color]
appear[color=blue]
> in the second file any number of times, but at least once.
>
> Now for my question. I need to be able to select all records in file 1[/color]
while[color=blue]
> joining to file 2 and selecting the most recent date. Am i making any
> sense?
>
> In other words, I need to see the transaction id, name, and most recent[/color]
date[color=blue]
> from file 2, but a simple join will return multiple instances of the
> transaction ID where I want to only see each ID once?
>
> Any help appreciated.[/color]

In relational DB we call them tables, not files.

Select a.tranid, a.name, b.tran_date
from master a, transaction b
where a.tranid = b.tranid
and b.tran_date = (select max(tran_date) from transaction c where c.tranid =
a.tranid)

Mark A wrote:
[color=blue]
> "cg_news" <cag_ng@-sbc-global.ten> wrote in message
> news:PL%Mc.2554$AY5.265@newssvr21.news.prodigy.com ...[color=green]
>> Hello,
>>
>> I have what I hope is a simple sql (DB2/AS400) question.
>> Assume 2 files exist. One is a transaction file with two fields, the
>> transaction ID and the transaction name. File two is keyed to file 1
>> through the transaction id and contains a transaction date. Since
>> transactions can be processed more than once, the transaction id can[/color]
> appear[color=green]
>> in the second file any number of times, but at least once.
>>
>> Now for my question. I need to be able to select all records in file 1[/color]
> while[color=green]
>> joining to file 2 and selecting the most recent date. Am i making any
>> sense?
>>
>> In other words, I need to see the transaction id, name, and most recent[/color]
> date[color=green]
>> from file 2, but a simple join will return multiple instances of the
>> transaction ID where I want to only see each ID once?
>>
>> Any help appreciated.[/color]
>
> In relational DB we call them tables, not files.
>
> Select a.tranid, a.name, b.tran_date
> from master a, transaction b
> where a.tranid = b.tranid
> and b.tran_date = (select max(tran_date) from transaction c where c.tranid
> = a.tranid)[/color]

Yeah, that is what i was experimenting with, but the problem i am having is
that i still receive multiple/duplicate transaction id no.s in the results,
they just all contain the same (most recent) dates. Im looking into UNIQUE
but its not quite clear to me how to make this work in this scenario?

Its late, im sure its something simple im missing here...

cg_news wrote:
[color=blue]
> Mark A wrote:
>[color=green]
>> "cg_news" <cag_ng@-sbc-global.ten> wrote in message
>> news:PL%Mc.2554$AY5.265@newssvr21.news.prodigy.com ...[color=darkred]
>>> Hello,
>>>
>>> I have what I hope is a simple sql (DB2/AS400) question.
>>> Assume 2 files exist. One is a transaction file with two fields, the
>>> transaction ID and the transaction name. File two is keyed to file 1
>>> through the transaction id and contains a transaction date. Since
>>> transactions can be processed more than once, the transaction id can[/color]
>> appear[color=darkred]
>>> in the second file any number of times, but at least once.
>>>
>>> Now for my question. I need to be able to select all records in file 1[/color]
>> while[color=darkred]
>>> joining to file 2 and selecting the most recent date. Am i making any
>>> sense?
>>>
>>> In other words, I need to see the transaction id, name, and most recent[/color]
>> date[color=darkred]
>>> from file 2, but a simple join will return multiple instances of the
>>> transaction ID where I want to only see each ID once?
>>>
>>> Any help appreciated.[/color]
>>
>> In relational DB we call them tables, not files.
>>
>> Select a.tranid, a.name, b.tran_date
>> from master a, transaction b
>> where a.tranid = b.tranid
>> and b.tran_date = (select max(tran_date) from transaction c where
>> c.tranid = a.tranid)[/color]
>
> Yeah, that is what i was experimenting with, but the problem i am having
> is that i still receive multiple/duplicate transaction id no.s in the
> results, they just all contain the same (most recent) dates. Im looking
> into UNIQUE but its not quite clear to me how to make this work in this
> scenario?
>
> Its late, im sure its something simple im missing here...[/color]

BLLAAHHH, DISTINCT seemed to work!
Thanks for the nudge in the right direction...