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.
3  10794 
"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)
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...
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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: poff |
last post by:
I have written a forum and am using the following query to search in it:
$query="select topics.tid,f_messages.messid from f_messages left join
topics on f_messages.tid = topics.tid where...
|
by: B. Fongo |
last post by:
I learned MySQL last year without putting it into action; that is why
I face trouble in formulating my queries. Were it a test, then you
would have passed it, because your queries did help me...
|
by: Paul Bramscher |
last post by:
Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes...
|
by: Tech |
last post by:
I have a table tblEmails where
the columns are id,list_id,address_id. I have many lists. I need to
find out
if a couple of lists (list_ids - 1000,1001,1002) have same
address_ids in common or...
|
by: Thomas Beutin |
last post by:
Hi,
i've a speed problem withe the following statement:
SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
FROM ot_adresse AS a, ot_produkt AS p
LEFT OUTER JOIN ot_kat_prod AS pz ON (...
|
by: Steve |
last post by:
Hi,
I am real new to databases and hoping someone can help.
Main-table is a huge spreadsheet that I imported into Access
Site-table has a bunch of addresses pulled from Main-table, quite a few...
|
by: PW |
last post by:
I've created an ASP application which uses an Access database.
I've created an outer join query, but for some reason the "Property_Def"
column is not aligning with the "ESPN" column. They should...
|
by: Dot Net Daddy |
last post by:
Hello,
I have set up a database for movies. In one table (Movies) I have
movie names, and production years, and also genres. In another table
(Directors), I keep the directors and the movies...
|
by: BD |
last post by:
Hi, all.
I need to enforce a one-to-many relationship on 2 tables, with a join
table.
Say the join table contains account information. It has cust_no and
acct_no. Both cust_no and acct_no are...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
