Re: Outer join (My solution based on Sybrand Bakker's response)

I found Sybrand's response most useful. I found that the MINUS clause worked:

• THIS WORKS!! RETURNS THE 34 EXPECT ROWS
• (supplied by Sybrand Bakker) 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' AND cud_cd_sys_id = cd_sys_id)

I also found that the NOT EXISTS clause useful:

• THIS WORKS!! RETURNS THE 34 EXPECT ROWS
• (supplied by Sybrand Bakker) 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' AND scud.cud_cd_sys_id = scd.cd_sys_id AND scd.cd_name = mcd.cd_name)

Even though I could not get it to work, the use of selecting from virtual tables was the most intriguing. I didn't know you could do that! Here is how I did it (once again I couldn't get it to work):

• This does not work, returns 35 USER_B displays
• (supplied by Sybrand Bakker) 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' AND scud.cud_cd_sys_id = scd.cd_sys_id) b WHERE a.cd_name = b.cd_name(+)

I also tried using the UNION clause with no success:

• This does not work, returns 35 USER_B displays
• (supplied by Sybrand Bakker) 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

In any case, thanks for the response!!

In article <85007e$6pj$1_at_nnrp1.deja.com>,   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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 06 2000 - 10:12:08 CST