Re: join in SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/06
Message-ID: <346bf4ae.182997937@newshost>#1/1

select a.value, c.value
  from a, c
 where a.c_id = c.id
   and not exists ( select NULL

                      from B
                     where b.c_id = c.id and b.a_id = a.id )

/

is one way to do it I believe...

On Thu, 06 Nov 1997 09:42:25 +0100, Gerard Le Comte <lecomte_at_sns.fr> wrote:

>Hi,
>
> I have a question on table joins :
>
>Consider 3 tables : A, B and C.
>A contains a reference to C and a value :
> a.c_id = c.id
>B only contains a reference to A and C :
> b.c_id = c.id and b.a_id = a.id
>C only contains a value
>
>Consider now, that I want to know all group (a.value;c.value)
>and that B is empty.
>
>----
>select a.value AV, c.value, AC from a, b, c
>where
>((a.c_id = c.id)
> OR (b.c_id = c.id and b.a_id = a.id)
>);
>----
>
>the result is always "no row selected" even if this
>request don't return "no row.." :
>----
>select a.value AV, c.value, AC from a, c
>where
>a.c_id = c.id;
>
>----
>I known that a good request could be :
>
>select a.value AV, c.value, AC from a, b, c
>where
>b.c_id = c.id and b.a_id = a.id;
>UNION ALL
>select a.value AV, c.value, AC from a, c
>where a.c_id = c.id;
>---
>But, it is not a simply way to build a more complex request.
>
>1) How could we build this request as the first request
>without "UNION ALL" ?
>2) Why the following request returns "no row selected" : ?
>--
>select * from a, b; -- a is not empty, b is empty
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.  

I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Thu Nov 06 1997 - 00:00:00 CST