locked
How to select * from a table where two columns dont match in another table RRS feed

  • Question

  • Hi!

    I have two tables:

    ServerList

    ID                 Servername               Loginame            logindate

    1                  Server1                      UserA                 28-11-19

    2                  Server1                      UserB                 27-11-19

    3                  Server2                      UserA                 28-11-19

    4                  Server2                      UserB                 27-11-19

    Exceptions

    ID                Loginname                  Server

    1                 UserB                          Server2

    2                 UserA                          Server1

    I want to select * from Serverlist where Loginname and Server name do not appear as a record in Exceptions table.

    I feel like this should be easy but I have been struggling for a few days to get it to work..

    Thanks,

    Zoe

                 

               

    Thursday, November 28, 2019 10:29 AM

Answers

All replies

  • SELECT * FROM ServerList WHERE NOT EXISTS (SELECT * FROM Exceptions E WHERE E.ID=ServerList.ID)

    OR

    SELECT * FROM ServerList WHERE NOT EXISTS (SELECT *

    FROM Exceptions E WHERE

    E.Loginname=ServerList.Loginname AND

    E.Servername=ServerList.Servername

     )


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, November 28, 2019 10:41 AM
    Answerer
  • Thanks Uri!
    Thursday, November 28, 2019 1:53 PM
  • For over 30 years, the netiquette on SQL forums has been to post DDL. But you, for some reason are exempt from basic manners and can be as rude as you want. WHY? You believe in a generic, magic, Kabbalah "id" thing that can identify servers, squids, automobiles, or any object in the universe. That's not part of RDBMS, that is part of magic.  A table by definition, must have a key, but since you don't bother to post DDL, we have to guess Finally, you don't even know how to write a date in SQL you're still using some vague, ambiguous local dialect. 

    CREATE TABLE Servers
    (server_name VARCHAR(225) NOT NULL,
     login_name VARCHAR(225) NOT NULL,
     login_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
     PRIMARY KEY(server_name, login_name));

    INSERT INTO Servers
    VALUES
    ('Server1', 'UserA', '2019-11-28'),
    ('Server1', 'UserB', '2019-11-27'),
    ('Server2', 'UserA', '2019-11-28'),
    ('Server2', 'UserB,  '2019-11-27');

    CREATE TABLE Something_Exceptions
    (login_name VARCHAR(225) NOT NULL,
    server_name VARCHAR(225) NOT NULL,
    PRIMARY KEY (????) );  -- A KEY IS NOT AN OPTION!!

    INSERT INTO Something_Exceptions
    VALUES
    ('UserB', 'Server2'),
    ('UserA', 'Server1');

    >> I want to select * from Servers where login_name and server_name do not appear as a record [sic]  in Something_Exceptions table<<

    Please don't ever use SELECT * in production code. It returns more columns than are necessary so it's expensive and since a change in any of the tables involved in the from clause of the select statement may change, a recompile will give you completely different result. The only place you should be using it is when you wish to refer to the table as a whole and not as a set of columns.

    It would also help if you knew the basics that rows are nothing like records. That usually covered in the first week of an intro to SQL class. If you taken that class, then around week three or four, you would have been introduced to relational division. This was one of the eight original operators Dr. Codd defined.

    SELECT server_name, login_name, login_date --- columns
    FROM Servers AS S 
     WHERE NOT EXISTS 
      (SELECT *  -- table as a whole
         FROM Something_Exceptions AS E
       WHERE E.login_name = S.login_name 
          AND E.server_name = S.server_name);

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, November 28, 2019 8:32 PM