Passing Mutiple values in SELECT/WHERE - Comparing 2 tables ???

  • Hello, I'm trying to compare 2 tables and list the record(s) that only exists in the first table. The first table is refreshed nightly using sysdatabases from all database servers. The second table is a static snapshot. 

    Both tables are identical in structure:

    database name (dbname)

    database server name (servername)

    database id (dbid)

    database create date (dbcreate)

    The same dbname (but with a different dbid and different servername) could exist on a different server ie: DBSERVERPROD.DB1 ... DBSERVERDEV.DB1 ... DBSERVERQA.DB1

    How can I pass 2 values at the same time in the WHERE clause?

    SELECT dbname, dbid, dbservername,  dbcreate

    FROM AuditSYSDB_DailyRefresh

    WHERE dbid, dbname NOT IN (SELECT dbid, dbname FROM AuditSYSDB_Static)

    ...

    Server: Msg 170, Level 15, State 1, Line 3

    Line 3: Incorrect syntax near ','.

    Any suggestions?

    Many thanks. Jeff

  • NOT EXISTS instead of NOT IN:

    SELECT dbname, dbid, dbservername,  dbcreate

    FROM AuditSYSDB_DailyRefresh As R

    WHERE NOT EXISTS (

      SELECT *

      FROM AuditSYSDB_Static  As S

      WHERE S.dbid = R.dbid

      AND     S.dbname=R.dbname

    )

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply