"not exists" SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: "not exists" SQL

  1. #1
    Join Date
    Feb 2006
    Posts
    5

    "not exists" SQL

    Gurus,

    I'm not getting the correct results from my query and I wanted to see if I could get some help.

    I want to get data from the bld table that doesn't exist in the kom table. Simple right? What am I doing wrong???

    select distinct bldbezc, bldkeyi from bld where not exists (
    select distinct b.bldbezc, b.bldkeyi, k.kombezc, k.komkeyi from bld b, kom k
    where k.komtyps = 3
    and k.komlf1i = b.bldkeyi );

    Zero results!

    I know for a fact there about 6,000 records!!!!

    Any help would be great ... Thank you

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Two options could be:

    Code:
    SELECT a1,
           a2
    FROM   table1 t1
    WHERE  NOT EXISTS (SELECT NULL
                       FROM   table2 t2
                       WHERE  t1.a1 = t2.a1
                       AND t1.a2 = t2.a2);
    
    
    SELECT *
    FROM   table1
    WHERE  (a1, a2) NOT IN (SELECT a1,
                                   a2
                       	FROM   table2
    	                WHERE  a1 IS NOT NULL 
                        	AND a2 IS NOT NULL );

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458

    Talking Minus

    Why not try this:
    Code:
    SELECT DISTINCT bldbezc, bldkeyi
               FROM bld
    MINUS
    SELECT DISTINCT k.kombezc, k.komkeyi
               FROM kom k
              WHERE k.komtyps = 3;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

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



Click Here to Expand Forum to Full Width