Re: Outer Join problem

lechkung wrote:
>
> Help for an outer join (I think)!
>
> I'm trying to select from a table the display names of USER_A doesn't
> have but USER_B does have. To get a list of displays that both USER_A
> and USER_B has I have the following select statement:
>
> SELECT cd_name
> FROM cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_A' AND
> cud_cd_sys_id = cd_sys_id AND
> cd_name IN ( SELECT cd_name FROM
> cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_B' AND
> cud_cd_sys_id = cd_sys_id )
>
> When using the above statement I get one cd_name back (which is
> correct).
>
> Now, to get a list of cd_name's back that USER_B has but not USER_A, I
> want to use an outer join like this:
>
> SELECT cd_name
> FROM cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_A' AND
> cud_cd_sys_id = cd_sys_id AND
> cd_name (+) IN ( SELECT cd_name FROM
> cil_displays, cil_user_displays
> WHERE
> cud_cu_id = 'USER_B' AND
> cud_cd_sys_id = cd_sys_id )
>
> Except, that I get "ORA-01799: a column may not be outer-joined to a
> subquery".
>
> Can anyone help me?!
>
> Greg Lechkun
> lechkung_at_dteenergy.com
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!

Check the MINUS in SQL. It should help...

select stuff from table where "user_A"
minus
select stuff from table where "user_B"

and vice-versa

-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue."