none
Compare 2 tables

    Question

  • Hi, i have 2 tables in my Database

    Table1 has the columns

    Servername - ip_address - status

    Table2 has the columns

    Servername - ipaddress

    I would like to compare both tables and query for servername that are in Table2(servername) but are not in Table1(servername)

    How can i achieve this?

    Thanks

    Wednesday, February 13, 2013 9:22 AM

Answers

  • select servername 

    FROM table1 a 

    where  NOT EXISTS (SELECT servername from table2 b where a.servername = b.servername)


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh


    • Edited by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Proposed as answer by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Marked as answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    Wednesday, February 13, 2013 9:26 AM
  • SELECT servername 

    FROM table2 a 

    where  NOT EXISTS (SELECT servername from table1 b where a.servername = b.servername)


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Marked as answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    Wednesday, February 13, 2013 9:32 AM

All replies

  • select servername 

    FROM table1 a 

    where  NOT EXISTS (SELECT servername from table2 b where a.servername = b.servername)


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh


    • Edited by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Proposed as answer by Kapil_KK Wednesday, February 13, 2013 9:30 AM
    • Marked as answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    Wednesday, February 13, 2013 9:26 AM
  • Are you looking for the below:

    Drop table T1,T2 create Table T1(servername Varchar(20),ip Varchar(20), status bit) Insert into T1 Select 'server1','213.234.234.234',1 Insert into T1 Select 'server10','123.123.234.234',1 create Table T2(servername Varchar(20),ip Varchar(20)) Insert into T2 Select 'server1','213.234.234.234' Insert into T2 Select 'server100','234.456.456.345' Select T2.* From T2 Left Join T1 On T2.servername = t1.servername Where T1.servername is null



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Junaid_Hassan Wednesday, February 13, 2013 9:51 AM
    Wednesday, February 13, 2013 9:29 AM
  • SELECT servername 

    FROM table2 a 

    where  NOT EXISTS (SELECT servername from table1 b where a.servername = b.servername)


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    • Marked as answer by slowscripter Wednesday, February 13, 2013 9:59 AM
    Wednesday, February 13, 2013 9:32 AM
  • thanks for the query it worked fine.

    Regards

    Wednesday, February 13, 2013 10:00 AM
  • SELECT servername FROM   table2
    EXCEPT
    SELECT servername FROM   table1 
    

    Wednesday, February 13, 2013 2:46 PM