locked
Comparing two tables by NOT NULL cells in different columns RRS feed

  • Question

  • Hi,

    We have two tables:

    Table1
    Servers|Databases|Users|Names
    Server1|Database1|User1|Name1
    Server2|Database2|User2|Name2
    Server3|Database3|User3|Name3
    Server4|Database4|User4|Name4
    Server5|Database5|User5|Name5

    Table2
    Servers|Databases|Users|Names
    NULL    |Database1|NULL |Name1
    NULL    |NULL        |User1|Name2
    NULL    |NULL        |NULL |Name3
    Server5|Database5|NULL |NULL

    I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:

    Results:
    Servers|Databases |Users |Names
    Server2|Database2|User2|Name2
    Server4|Database4|User4|Name4

    I used query like this:

    SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1
    EXCEPT
    SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c

    But for many rows (like 100 000) it takes ages to get results, any better way to work on this? 
    Thanks

    Thursday, October 22, 2015 2:36 PM

Answers

  • When you want to show us table data and DDL, use a method similar to my example. It makes it easier for us to help you.

    Try this solution out:

    DECLARE @Table1 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
    INSERT INTO @Table1 (servers, databases, users, names) VALUES
    ('Server1','Database1','User1','Name1'),
    ('Server2','Database2','User2','Name2'),
    ('Server3','Database3','User3','Name3'),
    ('Server4','Database4','User4','Name4'),
    ('Server5','Database5','User5','Name5')
    
    DECLARE @Table2 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
    INSERT INTO @Table2 (servers, databases, users, names) VALUES
    (NULL    ,'Database1'  ,NULL ,'Name1'),
    (NULL    ,NULL       ,'User1','Name2'),
    (NULL    ,NULL       ,NULL ,'Name3'),
    ('Server5' ,'Database5'  ,NULL ,NULL	)
    
    SELECT t1.*
      FROM @Table1 t1
        INNER JOIN @Table2 t2
    	  ON (t1.Servers = t2.Servers OR t2.Servers IS NULL)
    	  AND (t1.databases = t2.databases OR t2.databases IS NULL)
    	  AND (t1.users = t2.users OR t2.users IS NULL)
    	  AND (t1.names = t2.names OR t2.names IS NULL)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by jori5 Thursday, October 22, 2015 3:17 PM
    Thursday, October 22, 2015 2:43 PM

All replies

  • When you want to show us table data and DDL, use a method similar to my example. It makes it easier for us to help you.

    Try this solution out:

    DECLARE @Table1 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
    INSERT INTO @Table1 (servers, databases, users, names) VALUES
    ('Server1','Database1','User1','Name1'),
    ('Server2','Database2','User2','Name2'),
    ('Server3','Database3','User3','Name3'),
    ('Server4','Database4','User4','Name4'),
    ('Server5','Database5','User5','Name5')
    
    DECLARE @Table2 TABLE (Servers NVARCHAR(10), Databases NVARCHAR(10), Users NVARCHAR(10), Names NVARCHAR(10))
    INSERT INTO @Table2 (servers, databases, users, names) VALUES
    (NULL    ,'Database1'  ,NULL ,'Name1'),
    (NULL    ,NULL       ,'User1','Name2'),
    (NULL    ,NULL       ,NULL ,'Name3'),
    ('Server5' ,'Database5'  ,NULL ,NULL	)
    
    SELECT t1.*
      FROM @Table1 t1
        INNER JOIN @Table2 t2
    	  ON (t1.Servers = t2.Servers OR t2.Servers IS NULL)
    	  AND (t1.databases = t2.databases OR t2.databases IS NULL)
    	  AND (t1.users = t2.users OR t2.users IS NULL)
    	  AND (t1.names = t2.names OR t2.names IS NULL)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    • Marked as answer by jori5 Thursday, October 22, 2015 3:17 PM
    Thursday, October 22, 2015 2:43 PM
  • That works, thanks Patrick
    Thursday, October 22, 2015 3:16 PM