Re: Help with joins

From: Igor Skokin <ieaiskokin_at_surf.net>
Date: Tue, 16 Sep 2003 06:54:32 -0400
Message-ID: <bk6q5e$h5s$1@news.chatlink.com>

Raj,

a correlated sub-query might help. However, if you have more than one stop records for each start record, it will not work:

select distinct c1.USER_NAME as USER_NAME, c1.CALLED_STATION_ID, c1.TIME as START_T,
(SELECT c2.TIME from ACCOUNTING c2

              where c2.CLIENT_FUNCTION_TYPE like 'LNS'
              and c1.USER_NAME=c2.USER_NAME
              and c2.ACCT_STATUS_TYPE = 'Stop'

and (TO_DATE(c1.TIME,'yyyy/mm/dd HH24:mi:ss') <= TO_DATE(c2.TIME,'yyyy/mm/ddHH24:mi:ss')
) as STOP_T

from ACCOUNTING c1
where c1.CLIENT_FUNCTION_TYPE like 'LNS' and c1.ACCT_STATUS_TYPE like 'Start'

"Raj" <raj.kothary_at_thus.net> wrote in message news:bjl1ab$f8b$1$830fa79f_at_news.demon.co.uk...
> Hi,
>
> I think the problem I have can be solved using joins, but I just can't get
> my head around them! :O( I hope someone can help me.
>
> What I need to do is summed up by the following:
>
> select distinct c1.USER_NAME as USER_NAME, c1.CALLED_STATION_ID, c1.TIME
as
> START_T, c2.TIME as STOP_T
> from ACCOUNTING c1, ACCOUNTING c2
> where c1.CLIENT_FUNCTION_TYPE like 'LNS'
> and c2.CLIENT_FUNCTION_TYPE like 'LNS'
> and c1.USER_NAME=c2.USER_NAME
> and c1.ACCT_STATUS_TYPE like 'Start'
> and c2.ACCT_STATUS_TYPE in ('Stop', NULL)
> and (TO_DATE(c1.TIME,'yyyy/mm/dd HH24:mi:ss') <=
TO_DATE(c2.TIME,'yyyy/mm/dd
> HH2
> 4:mi:ss') OR c2.TIME IS NULL)
>
> I am trying to match the start record with a stop record, however the stop
> record may not exist, and that is where I'm having a problem. I still
want
> to return a row with a valid start time (c1), even if the stop time (c2)
> does not exist.
>
> Any ideas? I think maybe I need to use an outer join?
>
> Thanks in advance for any help provided.
>
> Regards,
> --
> Raj Kothary :: one|concept
> http://www.oneconcept.net
> raj_at_oneconcept.net
> + 44 (0)79 5647 2746
>
> oneconcept limited :: 2nd Floor West, 3-4A Little Portland Street, London
> W1A 5AG
>
> Confidentiality notice:
> The information transmitted in this email and/or any attached document(s)
is
> confidential and intended only for the person or entity to which it is
> addressed and may contain privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon
this
> information by persons or entities other than the intended recipient is
> prohibited. If you received this in error, please contact the sender and
> delete the material from any computer.
>
>
Received on Tue Sep 16 2003 - 05:54:32 CDT