Excluding rows query

[mimivdg2002]

I'm trying to excluding some rows from my resultset. Here is an example:

Table 1: Is the universal set of records (10,000) records
Table 2: Has a subset of the records that exist in Table 1 that meet condition1 (different from condition 2 below)
Table 3: Has a subset of the records that exist in Table 1 that meet condition2 (different from condition 1 above)

What I want to get is records from table 1 that are not in table 2 or not in table 3 something like:

Select * from table1 where table1.id not in (select id from table2) and table1.id not in (select id from table 3)

I'm not getting the result I want from this query. The result I get back gives me records from table1 that don't exist in table2 only. Can anybody please tell me what I'm doing wrong?

Thanks
M

 

[vongrunt]

What I want to get is records from table 1 that are not in table 2 or not in table 3 something like:

If I understand correctly, replace AND with OR... and for future enhancements wrap everything within WHERE clause into ().

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mimivdg2002]

Replacing AND with OR isn't going to do it. Sorry, I mispoke earlier, what I want to get are records from table 1 that are not in table 2 and that are not in table 3 (records in table 1 that are neither in table 2 nor in table 3).

Thanks
M

 

[vongrunt]

Not in T2 and not in T3... your original query looks OK.

You can always try the same with LEFT OUTER JOINs but...

Are you sure reason for this behavior isn't in data itself? Perhaps T3 is a "subset" of T2. Query is obviously simplified for posting purposes so we can't see that...

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[mimivdg2002]

You are exactly right, T2 is a subset of T3. So now what I have done is this:

Table 1: Is the universal set of records (10,000) records
Table 2: Has a subset of the records that exist in Table 1 that meet condition1 (different from condition 2 below)
Table 3: Has a subset of the records that exist in Table 1 that meet condition2 (condition 2 has additional criteria to exclude records that are in table 2)

So, Table 2 is no longer a subset of Table 3. What I want to do is get rows from table 1 that are not in table 2 and not in table 3. So my query says,

Select * from table1 where table1.id not in (select id from table2) and table1.id not in (select id from table 3)

but I still get the same result as before eventhough now table 2 is not a subset of table 3! Any ideas?

Thanks

 

[vongrunt]

My 2nd guess is that T2 and T3 still aren't exclusive. You can check that quickly with:

select count(*)
from table2 inner join table3 on table2.id=table3.id

... should return 0. Correct?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba