Huh? This list is for Firebird support, Transact-SQL is a completely different database server and I have no idea how that one works. One way to solve your question using Firebird (provided that the difference between TableB."Transaction" and your desired output is a spelling mistake):

SELECT A."Code", A."Description", B."Date", B."Transaction"
FROM TABLEA A
JOIN TABLEB B on A."Code" = B."Code"
WHERE NOT EXISTS(SELECT * FROM TABLEB B1
WHERE B1."Code" = B."Code"
AND B1."Date" > B."Date")

The double quotes can be removed if your field names are neither case sensitive nor a reserved word (like "Date").

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Yohanes Ongky Setiadji
Sent: 22. oktober 2008 09:54
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