Help with joins

From: Raj <raj.kothary_at_thus.net>
Date: Tue, 9 Sep 2003 18:08:36 +0100
Message-ID: <bjl1ab$f8b$1$830fa79f@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 09 2003 - 12:08:36 CDT