Anti-joins

[kidburla]

Sorry to bother you again but do you know how to create a join between two
tables and only pull off records which don't satisfy the join condition?

To illustrate, I created a test database which looks like the following:

Table1:

Key1 Name1
   1  Adam

Table2:

Key1 Key3
   1    1
   1    3

Table3:

Key3 Name3
   1   one
   2   two
   3 three
   4  four

Table2 is to be viewed as a link table between Table1 and Table3. So if I
want to pull off all records which satisfy this link, I run

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON (Table2.Key3=Table3.Key3);

This produces as expected:

Name1 Name3
 Adam   one
 Adam three

Now if I want to make the inverse query, namely one which looks like:

Name1 Name3
 Adam   two
 Adam  four

So I tried to do this query:

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON NOT (Table2.Key3=Table3.Key3);

But I got a weird result:

Name1 Name3
 Adam   two
 Adam three
 Adam  four
 Adam   one
 Adam   two
 Adam  four

So if I want to produce a query which looks like

Name1 Name3
 Adam   two
 Adam  four

Then what SQL code should I use?

Any help would be much appreciated.

[devikacs]

hi.
i'd like to help you, but i myself dont know joins well. i figure out the problem is caused because of the inner join you perform

in this example,

Table1:

Key1 Name1
1 Adam

Table2:

Key1 Key3
1 1
1 3


Table3:

Key3 Name3
1 one
2 two
3 three
4 four

the resultant table would be something like
1 Adam 1 one 1 1
1 Adam 2 two 1 1
1 Adam 3 three 1 1
1 Adam 4 four 1 1
1 Adam 1 one 1 3
1 Adam 2 two 1 3
1 Adam 3 three 1 3
1 Adam 4 four 1 3

So when you perform the check, it displays all rows where the keys dont match that of table2. i.e
1 Adam 2 two 1 1
1 Adam 3 three 1 1
1 Adam 4 four 1 1
1 Adam 1 one 1 3
1 Adam 2 two 1 3
1 Adam 4 four 1 3

Not knowing much about joins[i've used only inner join], i cant suggest how to get a table without those replications.

But as of now, this could be a possible solution. Join only table1 and table3.
1 Adam 1 one
1 Adam 2 two
1 Adam 3 three
1 Adam 4 four
using a cursor, compare this with each entry in table2. If it matches, do not print it.

I know its a inefficient way of doing it, but this is all i could think of.

[Vidhura]

Sorry to bother you again but do you know how to create a join between two
tables and only pull off records which don't satisfy the join condition?

To illustrate, I created a test database which looks like the following:

Table1:

Key1 Name1
   1  Adam

Table2:

Key1 Key3
   1    1
   1    3

Table3:

Key3 Name3
   1   one
   2   two
   3 three
   4  four

Table2 is to be viewed as a link table between Table1 and Table3. So if I
want to pull off all records which satisfy this link, I run

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON (Table2.Key3=Table3.Key3);

This produces as expected:

Name1 Name3
 Adam   one
 Adam three

Now if I want to make the inverse query, namely one which looks like:

Name1 Name3
 Adam   two
 Adam  four

So I tried to do this query:

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON NOT (Table2.Key3=Table3.Key3);

But I got a weird result:

Name1 Name3
 Adam   two
 Adam three
 Adam  four
 Adam   one
 Adam   two
 Adam  four

So if I want to produce a query which looks like

Name1 Name3
 Adam   two
 Adam  four

Then what SQL code should I use?

Any help would be much appreciated.

Try this

SELECT  distinct Table1.Name1, Table3.Name3
FROM Table1 
INNER JOIN Table2 ON Table1.Key1 =Table2.Key1
INNER JOIN Table3 ON Table3.key3 not in 
(select Table3.Key3 from Table3 
join  Table2 on  Table3.Key3 = Table2.Key3 and Table2.Key1=Table1.Key1)

[Infide]

Sorry to bother you again but do you know how to create a join between two
tables and only pull off records which don't satisfy the join condition?

To illustrate, I created a test database which looks like the following:

Table1:

Key1 Name1
   1  Adam

Table2:

Key1 Key3
   1    1
   1    3

Table3:

Key3 Name3
   1   one
   2   two
   3 three
   4  four

Table2 is to be viewed as a link table between Table1 and Table3. So if I
want to pull off all records which satisfy this link, I run

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON (Table2.Key3=Table3.Key3);

This produces as expected:

Name1 Name3
 Adam   one
 Adam three

Now if I want to make the inverse query, namely one which looks like:

Name1 Name3
 Adam   two
 Adam  four

So I tried to do this query:

SELECT Table1.Name1, Table3.Name3
FROM (Table1 INNER JOIN Table2 ON Table1.Key1=Table2.Key1) INNER JOIN
Table3 ON NOT (Table2.Key3=Table3.Key3);

But I got a weird result:

Name1 Name3
 Adam   two
 Adam three
 Adam  four
 Adam   one
 Adam   two
 Adam  four

So if I want to produce a query which looks like

Name1 Name3
 Adam   two
 Adam  four

Then what SQL code should I use?

Any help would be much appreciated.

The trick here is using a cross join since what you are asking for inherently doesn't exist, a relationship between those records.

select * from Table1 t1
cross join Table3 t3
where t3.key3 not in (SELECT t2.[key3]
from Table2 t2
inner join Table1 t1
on t1.key1 = t2.key1
)