Multiple Records Subquery..

Hi everybody,

I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Table 1

RecordID Description
20 Whew
21 Hi
21 There
21 Good Morning

Table 2
Date AccountNo RecordID Amount
04/02/2006 220 19 80.0
04/03/2006 221 20 290.0
04/04/2006 221 21 728.0
04/06/2006 223 23 200.3
04/07/2006 225 25 2893.20

den2005

select t2.AccountNo, t1.RecordId, t2.Description, t1.Amount

from Table1 t1 inner join Table2 t2 on t1.RecordId = t2.RecordId

where t2.AccountNo = 221 and t2.Date < '4/5/2006'

|||

Hi,

Thanks for replying, I f I do that teh results would be,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi 728.00

221 21 There 728.00

221 21 Good Morning 728.00

But I want to display shown below,

AccountNo RecordID Description Amount
221 20 Whew 290.00
221 21 Hi There Good Morning 728.00

Besides that is my first statement using inner join...

den2005

|||How do you determine that it is "Hi There Good Morning" and not "Good Morning Hi There" and not "There Hi Good Morning" etc.

Is there another column that will determine the required order ?

Do take not that, records are stored in database in no particular order. When returning the records, you have to determine the sequence by using ORDER BY.