Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Why Don't This Work??????/

    I HAVE THIS QUARY.

    SELECT tblUser.*
    FROM tblUser
    WHERE (LTRIM(RTRIM(tblUser.LastName))) NOT IN (SELECT LTRIM(RTRIM(tblGlobalDirUpdate.NAMLastName)) FROM tblGlobalDirUpdate)

    Why won't it return Users that are not in the other table? I know I have some.
    ExELECT tblUser.*
    FROM tblUser
    WHERE (LTRIM(RTRIM(tblUser.LastName))) = 'Hasenauer'

    This user is not in tblGlobalDirUpdate, but its not coming back.


    Please help...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure he's in tblUser?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Yes...
    Hasenauer is in the User table and its not in tblGlobalDirUpdate, so I need it to show up. There are about 6 or 7 users that are not in the tblGlobalDirUpdate that need to show up. Any ideas???

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know...works for me...

    cut and paste the code and tell me if it doesn't work for you...

    Code:
    USE Northwind 
    GO
     
    CREATE TABLE xtblUser(LastName varchar(50))
    CREATE TABLE xtblGlobalDirUpdate (NAMLastName varchar(50))
    GO
    
    INSERT INTO xtblUser(LastName)
    SELECT 'Hasenauer' UNION ALL
    SELECT 'Kaiser'
    
    INSERT INTO xtblGlobalDirUpdate(NAMLastName)
    SELECT 'Kaiser'
    GO
    
    
    SELECT *
      FROM xtblUser
     WHERE LastName 
    NOT IN (SELECT NAMLastName FROM xtblGlobalDirUpdate)
    GO
    
    DROP TABLE xtblUser
    DROP TABLE xtblGlobalDirUpdate
    GO
    maybe if you post the table DDL...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    The funny thing is this quary works:

    SELECT
    U.LastName
    FROM
    tblUser U
    LEFT JOIN tblGlobalDirUpdate G ON U.LastName = G.NAMLastName
    WHERE
    G.NAMLastName IS NULL

    So why does this works and the other one doesn't????

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you cut and paste my code in to a query analyzer window?

    It works for me....


    and btw...I like the left join, is null trick...

    And as a matter of fact, it's more effecient...

    cut and paste this code and turn on your show execution plan CTTRL+K

    Code:
    USE Northwind 
    GO
     
    CREATE TABLE xtblUser(LastName varchar(50))
    CREATE TABLE xtblGlobalDirUpdate (NAMLastName varchar(50))
    GO
    
    CREATE INDEX IDX1 ON xtblUser (LastName)
    CREATE INDEX IDX2 ON xtblGlobalDirUpdate (NAMLastName)
    GO
    
    INSERT INTO xtblUser(LastName)
    SELECT 'Hasenauer' UNION ALL
    SELECT 'Kaiser'
    
    INSERT INTO xtblGlobalDirUpdate(NAMLastName)
    SELECT 'Kaiser'
    GO
    
    
       SELECT *
         FROM xtblUser
        WHERE LastName 
       NOT IN (SELECT NAMLastName FROM xtblGlobalDirUpdate)
    
       SELECT *
         FROM xtblUser 
    LEFT JOIN xtblGlobalDirUpdate
           ON LastName = NAMLastName 
        WHERE  NAMLastName IS NULL
    GO
    
    DROP TABLE xtblUser
    DROP TABLE xtblGlobalDirUpdate
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thanks for the help, I got it.

  8. #8
    Join Date
    May 2003
    Posts
    26
    SELECT tblUser.*
    FROM tblUser
    WHERE (LTRIM(RTRIM(tblUser.LastName))) NOT IN (SELECT LTRIM(RTRIM(tblGlobalDirUpdate.NAMLastName)) FROM tblGlobalDirUpdate)
    Did you by chance have any NULL values in tblGlobalDirUpdate.NAMLastName? That would result in no return.

    (SELECT LTRIM(RTRIM(tblGlobalDirUpdate.NAMLastName)) FROM tblGlobalDirUpdate) will return a set of names, and NULL if one exists.

    In order to satisfy the main querry, tblUser.LastName has to be compared with each value in the subselect and a definitive 'NOT EQUAL TO' returned. When tblUser.LastName is compared with NULL, the result is UNKNOWN, not the NOT EQUAL TO needed to satisfy the query.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Donner
    Did you by chance have any NULL values in tblGlobalDirUpdate.NAMLastName? That would result in no return.

    (SELECT LTRIM(RTRIM(tblGlobalDirUpdate.NAMLastName)) FROM tblGlobalDirUpdate) will return a set of names, and NULL if one exists.

    In order to satisfy the main querry, tblUser.LastName has to be compared with each value in the subselect and a definitive 'NOT EQUAL TO' returned. When tblUser.LastName is compared with NULL, the result is UNKNOWN, not the NOT EQUAL TO needed to satisfy the query.
    Why don't you cut and paste the code I posted in QA, put show plan on, and see what it's doing....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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