Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!newsfeed.stueberl.de!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail From: "Raj" Newsgroups: comp.databases.oracle.misc Subject: Help with joins Date: Tue, 9 Sep 2003 18:08:36 +0100 Lines: 48 Message-ID: NNTP-Posting-Host: build-111.eng.demon.net X-Trace: news.demon.co.uk 1063127179 15627 194.217.90.111 (9 Sep 2003 17:06:19 GMT) X-Complaints-To: abuse@demon.net NNTP-Posting-Date: Tue, 9 Sep 2003 17:06:19 +0000 (UTC) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Priority: 3 X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MSMail-Priority: Normal Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:131472 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@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.