Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news-out.visi.com!petbe.visi.com!news.state.mn.us!not-for-mail
From: Turkbear <john.greco@dot.state.mn.us>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Help with joins
Date: Tue, 09 Sep 2003 12:23:34 -0500
Organization: Minnesota Governmental/Educational Agencies
Lines: 54
Message-ID: <3n2slv8mvr8b4bkrkr0ueum654olplc7bc@4ax.com>
References: <bjl1ab$f8b$1$830fa79f@news.demon.co.uk>
Reply-To: johng@mm.nospam.com
NNTP-Posting-Host: gateway.dot.state.mn.us
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: news.state.mn.us 1063128214 28459 156.98.4.11 (9 Sep 2003 17:23:34 GMT)
X-Complaints-To: news@news.state.mn.us
NNTP-Posting-Date: Tue, 9 Sep 2003 17:23:34 +0000 (UTC)
X-Newsreader: Forte Agent 1.93/32.576 English (American)
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:131473

"Raj" <raj.kothary@thus.net> wrote:

>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,
Time ( no pun intended) for an Outer Join!
Try:

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')  (+) 

The (+)  indicates that a value may not be in that table but  return the other table's row data  anyway -
It also means you can eliminate the test for c2.TIME's NULL since any c2.TIME not returned will be NULL.

( It has been awhile sine I used Outer joins with multiple WHERE clauses, so please check the syntax with the SqlPlus Docs)

hth,