Try this query



Select

A.Code,

A.Description,

tB.Transaction

from

(select

tB1.Code,

tB2.Transaction

from

(select

B.code,

max(B.Date) BDate

from B

group by B.Code ) tB1

inner join

(select

B.Code,

B.Transaction,

B.Date as BDate

from B) tB2

on tB2.Code = tB1.Code and

tB2.BDate = tB1.Bdate) as tB

inner join A

on A.Code = tB.Code



Hope this helps...



________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Yohanes Ongky
Setiadji
Sent: Wednesday, 22 October 2008 3:54 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] How to get only one record from transaction
table for each master data



I have 2 table A and B.

Table A is use for recording master data
Table B is use for recording transaction

example:

Table A
-------------------
Code | Description
-------------------
0001 John
0002 Mary
-------------------

Table B
--------------------------------------
Code | Date | Transaction
--------------------------------------
0001 | 01-01-2008 | Transaction John 1
0001 | 01-02-2008 | Transaction John 2
0001 | 01-03-2008 | Transaction John 3
0002 | 01-01-2008 | Transaction Mary 1
0002 | 15-02-2008 | Transaction Mary 2
--------------------------------------

Now I want to get record from Table A and Table B and get only the last
transaction of each Code, like this:

----------------------------------------------------
Code | Description | Date | Transaction
----------------------------------------------------
0001 | John | 01-03-2008 | John Transaction 3
0002 | Mary | 15-02-2008 | Mary Transaction 2
----------------------------------------------------

Can anyone teach me how to use Transact-SQL command to get result like
that?

Thanks,

Ongky

New Email addresses available on Yahoo!
Get the Email name you've always wanted on the new @ymail and
@rocketmail.
Hurry before someone else does!
http://mail.promotions.yahoo.com/newdomains/aa/
<http://mail.promotions.yahoo.com/newdomains/aa/>



Internal Virus Database is out of date.
Checked by AVG.
Version: 8.0.100 / Virus Database: 270.8.0/1719 - Release Date:
10/10/2008 4:08 PM




[Non-text portions of this message have been removed]