Simple Exists Question 
Author Message
 Simple Exists Question
What should the following query return where column c2 is a
subset of column c1?

select c1 from t1 where not exists (select c2 from t1, t2 where
t1.c1 = t2.c2)

I want it to return all values of c1 that are not in c2.

* Sent from RemarQ http://www.***.com/ The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!



Fri, 11 Oct 2002 03:00:00 GMT
 Simple Exists Question

Hi,

I would expect a syntax error, the references to t1 in the where clause
of your subquery are ambiguous (the compiler won't know whether you mean
t1 in the subquery or t1 in the outer select).

The correct query would be

select c1
from t1 a
where not exists ( select c2 from t1 b
                   where a.c1 = b.c2 )

You could also use an outer join and check only for rows that
have NULL values for c2.

Miro

Quote:

> What should the following query return where column c2 is a
> subset of column c1?

> select c1 from t1 where not exists (select c2 from t1, t2 where
> t1.c1 = t2.c2)

> I want it to return all values of c1 that are not in c2.

> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!



Fri, 11 Oct 2002 03:00:00 GMT
 Simple Exists Question
Thanks for your reply. Your SQL query worked! When I ran my
query, I didn't receive an error. It either returned all rows or
none of the rows. Thanks again!

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!



Fri, 11 Oct 2002 03:00:00 GMT
 Simple Exists Question
Assuming t1 and t2 have at least one matching row, your query will
return nothing.  What you want to do is use a "correlated subquery".
This is where the subquery references a table from outside the
subquery.  It actually looks very close to what you wrote - the only
change is in the subquery's FROM clause (t1 was removed):

   SELECT c1 FROM t1 WHERE NOT EXISTS
      (SELECT c2 FROM t2 WHERE t1.c1 = t2.c2)

Hope this helps.
--
___________________________________________________________________________
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs



Fri, 11 Oct 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. probabley simple question - how to replicate existing database for another application's use

2. Simple SQL Question, Not Exists

3. SQL7, SIMPLE SIMPLE SIMPLE question

4. Simple Question...hopefully simple answer

5. A simple question, really simple

6. Simple Set statement this is very simple question

7. VB5 Pro Masked Edit control - A simple, simple question

8. HELP! simple question need simple answer

9. Simple simple ADO question

10. Simple SQL statement (Insert entry if entry doesnt exist)

11. Newbie: CATALOG sample question (SIMPLE question)

12. need to copy existing Access data into existing SQL tables, maintaining identity values


 
Powered by phpBB® Forum Software