SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Table-Join and NOT-Operator ??

    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

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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”.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Using

    Code:
    from a inner join b on a.id = b.id
    is not potentially faster than

    Code:
    from a,b where a.id = b.id
    As the queries are equivivalent they can be optimized exatly the same way.

  5. #5
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by smordi
    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)


    Quote Originally Posted by smordi
    PLEASE WRITE ME IN THE EXACT WAY HOW TO WRITE ..
    please don't shout

    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, sorry for shouting.
    Thanks thanks thanks @ all!

    It is working


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •