Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    11

    Unanswered: Select * where Table1.uid != Table2.uid????

    I have a table of users, and when I join it with a second table (details table) on uid I get a partial list, which is fine. But I would like the remaining list of users who were not included. What is the easiest way to compare two tables and give me the results where the UID's dont match??

    Im sure this is easy but i for some reason cant get it to work, cant join on uid and then say uid ! = uid..

    i tried doing a select uid from table1 right join table2 on t1.uid = t2.uid where t1.uid <> t2.uid.

    but no go....

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Check for null UID in tb2

    WHERE tb2.UID IS NULL
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Feb 2009
    Posts
    11
    its not null...

    sometimes in t2 there is a uid that for some reason is not in t1. So i want all records from t1 where for somereason they dont match up

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    SELECT nz(t1.uid,t2.uid) from table1 inner join table2 on t1.uid = t2.uid WHERE t1.uid IS NULL or t2.uid IS NULL

    You want the uid from t1 OR t2 is that correct? Do you want to identify the table with the missing ID also?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Feb 2009
    Posts
    11
    gareth appreciate your help thus far, but I think your misunderstanding...

    table 1 has a UID column, table 2 has a UID column.

    say table 2 has 600 rows, table 1 has 1000 rows.
    those 600 UID in table 2 are also UID in table 1, but I dont care about them. I want the UID in table 1 that are NOT in table 2 but UID is the only field between that two that matches up.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Raysoc
    I want the UID in table 1 that are NOT in table 2
    you were fairly close with this --

    select uid from table1 right join table2 on t1.uid = t2.uid where t1.uid <> t2.uid

    what you want is this --

    select table1.uid from table1 left join table2 on t1.uid = t2.uid where t2.uid is null

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    11
    okay I'm not getting why i would write null. there are NO null or blank entries it would simply be a number that is not in the other table...

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forget NULL in the table(s)
    look for NULL in the JOIN... just try it - you'll see.

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    take a couple of minutes and find a tutorial for LEFT OUTER JOIN -- all sql tutorials cover this

    NULL is use for columns from the right table in unmatched rows from the left table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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