Re: Help with joins

From: Turkbear <john.greco_at_dot.state.mn.us>
Date: Tue, 09 Sep 2003 12:23:34 -0500
Message-ID: <3n2slv8mvr8b4bkrkr0ueum654olplc7bc@4ax.com>

"Raj" <raj.kothary_at_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, Received on Tue Sep 09 2003 - 12:23:34 CDT