Join Question 1

[guber]

I have 2 tables like this:

Table1 | Table2
|
A1 | B1
A2 | B2
A3 | B3
A4 | B4

I need to know waht rows are in Table 1 that there are't in Table 2

I tried this:
select * from Table1, Table2 where
a1 not in (select b1 from table2)
and a2 not in (select b2 from table2)
and a3 not in (select b3 from table2)
and a4 not in (select b4 from table2)

but this didn't work...

Someone can help me ??

Thanks !!

 

[Zathras]

A clearer example with sample data would help us to understand exactly what you need.

What are the primary keys to the tables?

Are you saying that normally you would expect to find records from both tables that match on four columns (A1=B1, A2=B2, A3=B3 and A4=B4)?

Are there only four columns in each table?

It is difficult (at least for me) to grasp what you really are asking.

 

[guber]

I´m sorry, I'll try to better explain...

These 4 fields are the key ..

And I have to mach the 4 fields.

Example:

Table 1

A1 A2 A3 A4
1 1 1 1
2 2 2 2
3 3 3 3

Table 2

A1 A2 A3 A4
2 2 2 2
3 3 3 3


I need a select that returns only the first line of the table 1.


Thanks !!

 

[swampBoogie]

select * from table1
where not exists(
select * from table2
where b1 = table1.a1
and b2 = table1.a2
and b3 = table1.a3
and b4 = table1.a4)

 

[guber]

It works !!!


Thanks a lot !!!!

 

[Zathras]

If your tables are very large, this might find the answers faster, depending on your indexes:

create table #T1 (A1 int, A2 int, A3 int, A4 int, D1 varchar(10))
create table #T2 (A1 int, A2 int, A3 int, A4 int, D1 varchar(10))

insert into #T1 values(1,1,1,1,'rec 1111')
insert into #T1 values(2,2,2,2,'rec 2222')
insert into #T1 values(3,3,3,3,'rec 3333')

insert into #T2 values(2,2,2,2,'rec 2222')
insert into #T2 values(3,3,3,3,'rec 3333')

select J.* from #T1 J
  left outer join #T2 K ON J.A1=K.A1 AND J.A2=K.A2
                       AND J.A3=K.A3 AND J.A4=K.A4
where k.D1 is null