SQL - JOINS Problem 1

[kingz2000]

hi ,

I have a simple SQL problem, which I don't seem to get my head around.
I have 2 Tables(Tab1 and Tab2) looking as follows:

Tab1
----

A B C D
------------------
1 1 10 SQ
1 2 20 TR
1 3 30 EE
2 1 40 FR


Tab2
----

A B
-------
1 2
1 3

I want from table 1 the rows which are not contained in tab2, hence the first and last row. How do I do this??
The problem is that there are 2 fields as identifier. Otherwise I would obviously just write:

select * from tab1 where a not in (select a from tab2)

I hope my question is clear.

Thanks in advance,

Kingsley

 

[jrbarnett]

SELECT tab1.*
FROM tab1 left outer join tab2 ON
(tab1.A = tab2.A AND tab1.B = tab2.B)
WHERE tab2.A IS NULL and tab2.B IS NULL

John

 

[kingz2000]

Thank you very much!!!

...It works and everything, but I am confused by the purpose of the where clause.Just to help me understand, why
WHERE tab2.A IS NULL and tab2.B IS NULL???

 

[jrbarnett]

A left join will show all the rows on one side of the join (the one before "left"). Those with no match will have a null value on the right hand side of the join.

Therefore, what the where clause is doing is excluding those joined fields with null values -ie the records in tab2.
You shouldn't need to join on both fields, but if they are used together as a composite primary key then its safer to include all fields.

John

 

[kingz2000]

thanks

 

[lespaul]

another way using the syntax you know of is to concatenate the fields together:

select * from tab1 where a&b not in (select a&b from tab2)

disclaimer: it may be the + and not the &
I use another flavor of SQL that actually uses || as the concatenator so I'm not positive which one JET SQL uses.


Leslie

Have you met Hardy Heron?