Table-Join and NOT-Operator ??

[smordi]

Dear MySQL-Fans

There are 2 tables:
table1
-------------
admnr | fbnr
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2

table2
----------------------
fbnr | fbname
1 | mathematics
2 | chemistry
3 | physics
4 | informatics


Now the problem:
I need all 'fbnr' and 'fbname' where 'admnr' 2 is NOT in !
At the end there should be:

fbnr | fbname
3 | physics
4 | informatics

I read and thought and.... but my brain still doesnt give me an answer
I tried this:
SELECT table2.fbnr, table2.fbname FROM table2, table1 WHERE table2.fbnr = table1.fbnr AND NOT (table1.admnr = 2)
Then he gives me 1, 2, 3, 4 of course. I dont know how to do it. Please help me

[longneck]

see http://dev.mysql.com/doc/mysql/en/join.html

specifically:

#

If there is no matching record for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

mysql> SELECT table1.* FROM table1
-> LEFT JOIN table2 ON table1.id=table2.id
-> WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL. See Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN”.

[longneck]

oh, and make an effort to learn the JOIN syntax. JOINs are much easier to understand while debugging and potentially faster due to additional optimizations available to JOINs.

[swampBoogie]

Using

from a inner join b on a.id = b.id

is not potentially faster than

from a,b where a.id = b.id

As the queries are equivivalent they can be optimized exatly the same way.

[smordi]

Thank you very much !

Where can I find ' Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN” ' ?

Ok... I will post again the 2 tables in their original form:

TABLE1: BEREICH
----------------
fbnr | fbname
1 | mathmetics
2 | chemistry
3 | physics
4 | informatics

TABLE2: ADM_FB
----------------
admnr | fbnr
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2


I need from TABLE1: BEREICH :
fbnr | fbname
3 | physics
4 | informatics

Like I know you can write in Oracle the following query:

SELECT a.fbnr, a.fbname
FROM bereich a
WHERE fbnr NOT IN (SELECT admnr
FROM adm_fb b
WHERE admnr = 2);

I tried it like you wrote in the following query:

SELECT a.fbnr, a.fbname
FROM bereich a LEFT JOIN adm_fb b
ON b.fbnr = a.fbnr
WHERE b.admnr IS NULL

No failure, but NO RESULTS
Every columns are NOT NULL.
PLEASE WRITE ME IN THE EXACT WAY HOW TO WRITE ...

Thanks a lot

[r937]

Where can I find ' Section 7.2.9, “How MySQL Optimizes LEFT JOIN and RIGHT JOIN” ' ?

you can find that on the mysql.com web site (use the search box in the top right corner)

PLEASE WRITE ME IN THE EXACT WAY HOW TO WRITE ..

please don't shout

select BEREICH.fbnr 
     , BEREICH.fbname
  from BEREICH
left outer
  join ADM_FB
    on BEREICH.fbnr = ADM_FB.fbnr  
   and ADM_FB.admnr = 2
 where ADM_FB.fbnr is null

[smordi]

Hey, sorry for shouting.
Thanks thanks thanks @ all!

It is working