Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2006
    Posts
    87

    Unanswered: compare two tables and displaying the records not existing on the other

    Hi everybody

    have this task of comparing two tables and displaying records from table1 which does not exist on the other TABLE 2... my query i'm using below doesn't give me any result inspite of having records that doesn't exist on the other both of the tables have personsid as a field


    SELECT T1.PERSONSID
    FROM TABLE1 T1
    WHERE PERSONSID NOT IN (SELECT PERSONSID FROM TABLE2 T2)

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops, forget that. I was thinking of something else....

    Please give an example of a value not returned that should be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Aug 2006
    Posts
    87
    am using mssql 2000

    personsid = 41 this exist on one and not on the other and more ..

    thanks


    Quote Originally Posted by pootle flump
    Oops, forget that. I was thinking of something else....

    Please give an example of a value not returned that should be.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT    TOP 1 PERSONSID 
    FROM    TABLE1
    WHERE    PERSONSID = 41
    
    SELECT    TOP 1 PERSONSID 
    FROM    TABLE2
    WHERE    PERSONSID = 41
    Please could you tell me the result of the below?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2006
    Posts
    87
    SELECT TOP 1 PERSONSID
    FROM TABLE1
    WHERE PERSONSID = 41

    result is
    personsid
    41

    SELECT TOP 1 PERSONSID
    FROM TABLE2
    WHERE PERSONSID = 41

    result
    personsid (no result)


    Quote Originally Posted by pootle flump
    Code:
    SELECT    TOP 1 PERSONSID 
    FROM    TABLE1
    WHERE    PERSONSID = 41
    
    SELECT    TOP 1 PERSONSID 
    FROM    TABLE2
    WHERE    PERSONSID = 41
    Please could you tell me the result of the below?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You ran those two queries? You didn't just guess the results? Because it should work fine.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Aug 2006
    Posts
    87
    yes i ran it right!!

    Quote Originally Posted by pootle flump
    You ran those two queries? You didn't just guess the results? Because it should work fine.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would always use NOT EXISTS over NOT IN as it is (typically) more efficient. It is not logically the same, but it should return the same result as your query. For giggles, try this.
    Code:
    SELECT PERSONSID
    FROM TABLE1
    WHERE NOT EXISTS (SELECT NULL FROM TABLE2 WHERE TABLE2.PERSONSID = TABLE1.PERSONSID)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    And try this:
    Code:
    SELECT    PERSONSID, source = MAX(source)
    FROM    
            (
                SELECT    PERSONSID, source = 'TABLE1'
                FROM    TABLE1
                UNION ALL
                SELECT    PERSONSID, source = 'TABLE2'
                FROM    TABLE2
            ) AS uniont
    GROUP BY PERSONSID
    HAVING COUNT(*) = 1
    ORDER BY CASE WHEN PERSONSID = 41 THEN 0 ELSE 1 END
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2006
    Posts
    87
    yes with this did the trick and i got all the records that are not on the other
    thanks


    Quote Originally Posted by pootle flump
    I would always use NOT EXISTS over NOT IN as it is (typically) more efficient. It is not logically the same, but it should return the same result as your query. For giggles, try this.
    Code:
    SELECT PERSONSID
    FROM TABLE1
    WHERE NOT EXISTS (SELECT NULL FROM TABLE2 WHERE TABLE2.PERSONSID = TABLE1.PERSONSID)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I want to get to the bottom of this. Please try:
    Code:
    SELECT T1.PERSONSID
    FROM TABLE1 T1
    WHERE T1.PERSONSID NOT IN (SELECT T2.PERSONSID FROM TABLE2 T2)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Aug 2006
    Posts
    87
    with this query it didn't give any result


    Quote Originally Posted by pootle flump
    I want to get to the bottom of this. Please try:
    Code:
    SELECT T1.PERSONSID
    FROM TABLE1 T1
    WHERE T1.PERSONSID NOT IN (SELECT T2.PERSONSID FROM TABLE2 T2)

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm.
    I'm stumped then.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Aug 2006
    Posts
    87
    thanks for the help well i usually use not in and usually works fine but not with this two tables...



    Quote Originally Posted by pootle flump
    Hmmm.
    I'm stumped then.

Posting Permissions

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