At 03:16 AM 4/08/2004 +0000, you wrote:

>Just to clarify a bit further in the following result:
>
>select count(*)
>from ActionLog
>where Action_DateTime > '02-AUG-2004'
>
>the result is 19--those are the 19 records I want (one ActionLog
>record has a client_id of null). If I use the following:
>
>select count(*)
>from ClientInfo CI
>right outer join ActionLog AL on CI.Client_ID = AL.Client_ID
>where Action_DateTime > '02-AUG-2004'
>
>I get the same count of 19--perfect. If I add CompanyInfo (to get the
>companies name)
>
>select count(*)
>from ClientInfo CI
>right outer join ActionLog AL on CI.Client_ID = AL.Client_ID
>right join CompanyInfo CO
>on CI.Company_ID = CO.Company_ID
>where Action_DateTime > '02-AUG-2004'
>
>I only get 18 records--the ActionLog record with a null CLient_ID is
>no longer included (confirmed by changing result set).
>
>Can I get the Company name in the SQL?

If you want to ensure that you get an ActionLog record represented no
matter what else happens, make it the leftmost stream.

How you define the streams totally depends on what you want in the
output. If you don't care whether the same actionlog record recurs
multiple times in the output, then a right join on the other table is
OK; otherwise, use a left join. With the left join, you will get nulls in
the ClientInfo columns if the ActionLog can't find a matching Client_ID in
ClientInfo.

Unless you have clients with no Company_ID, then you want an inner join
between ClientInfo and CompanyInfo. In your final case, btw, the missing
record was due to using a right join between ClientInfo and CompanyInfo --
so I guess, in fact, that the ClientInfo-CompanyInfo relationship is
optional also. But use a left join here to get the Client, regardless of
whether or not a Company relationship exists.

And yes, of course you can get the Company name in the output.

Try this (still only able to guess about what you *really* want in the
output). If there are 19 rows in ActionLog that meet the search criterion,
you will get exactly 19 rows of output:

select
blah....,
CO.CompanyName,
blah...
from ActionLog AL
left join ClientInfo CI
on CI.Client_ID = AL.Client_ID /* nulls in client fields if no match */
left join CompanyInfo CO
on CI.Company_ID = CO.Company_ID /* nulls in company fields if no match */
where AL.Action_DateTime > '02-AUG-2004' <-- note you must use the aliases
in the search clause (and also in group by and order by clauses, if present)

/heLen