469,281 Members | 2,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

SQL join/UNIQUE question?

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.
Nov 12 '05 #1
3 10658
"cg_news" <cag_ng@-sbc-global.ten> wrote in message
news:PL****************@newssvr21.news.prodigy.com ...
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.


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)
Nov 12 '05 #2
Mark A wrote:
"cg_news" <cag_ng@-sbc-global.ten> wrote in message
news:PL****************@newssvr21.news.prodigy.com ...
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.


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)


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...
Nov 12 '05 #3
cg_news wrote:
Mark A wrote:
"cg_news" <cag_ng@-sbc-global.ten> wrote in message
news:PL****************@newssvr21.news.prodigy.com ...
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.


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)


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...


BLLAAHHH, DISTINCT seemed to work!
Thanks for the nudge in the right direction...
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by poff | last post: by
1 post views Thread by Paul Bramscher | last post: by
2 posts views Thread by Tech | last post: by
1 post views Thread by Steve | last post: by
6 posts views Thread by PW | last post: by
5 posts views Thread by Dot Net Daddy | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.