At 04:04 PM 13-06-02 +0200, you wrote:

>Helen, if I run the query over one of the tables without any reference to
>the 2nd one, the number of records matching the criteria is 764. When I run
>your join query, I get 7834 records. I should only have the 764.

I am totally unsurprised. You insisted on the left join - I explained what
you could expect from it. <g>

> The reason I need to do this is to get the customers account no which forms
> part of the original transaction. This is unfortunately the only way I

can get this

> info. It is for a special report. TransRef is not a primary key - it

can't be.

Your description yesterday didn't mention that the actn code for the left
side of the join set was important: so do you need to eliminate rows
where 'Bank - Sanlam' has some other actn code than 'D', then?

The following ought to produce one row for each 'Bank - Sanlam' row having
actn 'D' which can find an accountid having a matching transref and an actn
'C':

select a.transref, b.amount, b.actn,
a.accountid as accountid1,
b.accountid as accountid2
from ATable a
join ATable b
on a.transref = b.transref <--- this is your join criterion
where a.accountid = 'Bank1' <--- this is a selection criterion
and b.actn = 'C' <--- and so is this
and a.actn = 'D' <---- and so is this

>A query over the one table yields 764 -->
>
>SELECT count(transref)
>FROM genledger
>WHERE accountid = 'Bank - Sanlam'
>AND actn = 'D'
>AND transdate between cast('04/01/2002' as date) and cast('04/30/2002' as
>date)
>
>The suggested join query yields 7834 -->

Not surprising - the left join provided you with all those unwanted
rows. The inner join will get the same number of rows, as your count of
left-side rows, or less.
(You will get less, if there are left-side rows which can't find a
right-side match).

cheers,
heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________