Page 1 of 1

How to do this in mySQL?

Posted: Thu Jan 16, 2003 4:36 am
by Brother
Hallo,

I have this problem:

One table contains list of debts, identified by dbtID and other fields.
Other table contains list of cashflows, with field dbtID identifiying the
corresponding debt, cfDate identifying the date of cashflow, and cfAmount
describing the amount (could be both positive and negative).

Now, I need to create a SQL statement to get list of debts, with the
following columns:
column 1: Debt ID (dbtID);
column 2: Original balance (i.e. the earliest corresponding balance)

As far I can see, I don't have straightfowrward way to create a list of
cashflows, grouped by dbtID, and taking the cfAmount value from the record
where cfDate is the smallest.

I am looking for an ANSI-compliant SQL string, so that it would work on
other database formats, too.

Thanks for your help!

-- Brother

Posted: Thu Jan 16, 2003 10:31 am
by BDKR
Since the querstion was "How to do this in mySQL?", why don't you consult the MySQL manual? There is a tutorial chapter in there that could give you a lot of tips, including the use of group by and order by (sounds like you'll need both). Another thing to remember is that you may also write table names and table fields as tuples. In some cases you will need too or else the query will be considered vauge by the MySQL engine. An example would be.

Code: Select all

SELECT table1.dork_id, table1.dork_name, table1.dork_age, table2.dork_id, table2.p_order
FROM table1, table2 WHERE yada yad yada....
Cheers,
BDKR