Re: Outer join

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 05 Jan 2000 18:30:49 GMT
Message-ID: <38738bd3.829017@news.demon.nl>

On Wed, 05 Jan 2000 17:49:40 GMT, lechkung_at_dteenergy.com 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 via Deja.com http://www.deja.com/
>Before you buy.

Some variant resolutions

   SELECT cd_name

     FROM cil_displays, cil_user_displays
     WHERE
       cud_cu_id = 'USER_A' AND
	 cud_cd_sys_id = cd_sys_id

   UNION
   SELECT cd_name
   FROM cil_displays

      , cil_user_displays
   WHERE

         cud_cu_id = 'USER_B'  
     AND cud_cd_sys_id = cd_sys_id

for statement 1
which means

   SELECT cd_name

     FROM cil_displays, cil_user_displays
     WHERE
       cud_cu_id = 'USER_B' AND
	 cud_cd_sys_id = cd_sys_id

   MINUS
   SELECT cd_name
   FROM cil_displays

      , cil_user_displays
   WHERE

         cud_cu_id = 'USER_A'  -- note the difference
     AND cud_cd_sys_id = cd_sys_id

for statement 2

or

   SELECT cd_name

     FROM cil_displays mcd, cil_user_displays mcud
     WHERE
       cud_cu_id = 'USER_B' AND
	 cud_cd_sys_id = cd_sys_id

   and not exists
   (
   SELECT cd_name
   FROM cil_displays scd

      , cil_user_displays scud
   WHERE

         scud.cud_cu_id = 'USER_A'  -- note the difference
     AND scud.cud_cd_sys_id = scd.cd_sys_id
     and scd.cd_name = mcd.cd_name)

or

   select a.cd_name
   from
   (
   SELECT cd_name

     FROM cil_displays mcd, cil_user_displays mcud
     WHERE
       cud_cu_id = 'USER_B' AND
	 cud_cd_sys_id = cd_sys_id

   ) a
,

   (
   SELECT cd_name
   FROM cil_displays scd

      , cil_user_displays scud
   WHERE

         scud.cud_cu_id = 'USER_A'  -- note the difference
     AND scud.cud_cd_sys_id = scd.cd_sys_id
     and scd.cd_name = mcd.cd_name) b

  where a.cd_name = b.cd_name(+)

Oracle can be fun!!
You need to use outer join in join constructs (from tablea, tableb) only! A subquery is not a join.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Jan 05 2000 - 12:30:49 CST