By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

SQL join/UNIQUE question?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
"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

P: n/a
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

P: n/a
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.