Re: BEGINNERS SQL QUESTION

davout schrieb:
>
> I have two tables....
>
> MyTran
> ---------------
> TranUID INTEGER
> TranName VARCHAR
>
> MyTranStatus
> ------------------------
> TranUID INTEGER
> EntryDate DATE
> Status INTEGER
>
> The data is something like....
>
> MyTran
> --------------
> TranUID TranName
> 1 Trans#1
> 2 Trans#2
>
> MyTranStatus
> --------------------
> TranUID EntryDate Status
> 1 23/1/99 6
> 1 2/2/99 7
> 1 10/2/99 5
> 2 31/1/99 6
> 2 28/2/99 4
>
> How can I create a query whereby I can get all the records back from
> 'MyTran' with the most recent entry from the related MyTranStatus table?
> Like...
>
> 1 Trans#1 10/2/99 5
> 2 Trans#2 28/2/99 4
>
> Any ideas?

SELECT

	t.TranUID,
	t.TranName,
	m.EntryDate,
	m.Status
FROM
	MyTran t,
	MyTranStatus m
WHERE
	t.TranUID=m.TranUID
AND
	m.EntryDate = 	(SELECT
				MAX(EntryDate) EntryDate
			FROM
				MyTranStatus mt
			WHERE
				mt.TranUID = m.TranUID	
			)

HTH
MAtthias
--
grema_at_t-online.de

Protect privacy, boycott Intel: http://www.bigbrotherinside.org Received on Thu Mar 04 1999 - 03:56:47 CST