CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012
Erik
Erik

Reputation: 61

SQL Logic: Finding Non-Duplicates with Similar Rows

I'll do my best to summarize what I am having trouble with. I never used much SQL until recently.

Currently I am using SQL Server 2012 at work and have been tasked with trying to find oddities in SQL tables. Specifically, the tables contain similar information regarding servers. Kind of meta, I know. So they each share a column called "DB_NAME". After that, there are no similar columns. So I need to compare Table A and Table B and produce a list of records (servers) where a server is NOT listed in BOTH Table A and B. Additionally, this query is being ran against an exception list. I'm not 100% sure of the logic to best handle this. And while I would love to get something "extremely efficient", I am more-so looking at something that just plain works at the time being.

SELECT  *
FROM    (SELECT
            UPPER(ta.DB_NAME) AS [DB_Name]
        FROM
            [CMS].[dbo].[TABLE_A] AS ta
        UNION
        SELECT
            UPPER(tb.DB_NAME) AS [DB_Name]
        FROM
            [CMS].[dbo].[TABLE_B] as tb
        ) AS SQLresults
WHERE NOT EXISTS (
    SELECT *
    FROM
        [CMS].[dbo].[TABLE_C_EXCEPTIONS] as tc
    WHERE
        SQLresults.[DB_Name] = tc.DB_NAME)
ORDER BY    SQLresults.[DB_Name]

Upvotes: 0

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method uses union all and aggregation:

select ab.*
from ((select upper(name) as name, 'A' as which
       from CMS.dbo.TABLE_A
      ) union all
      (select upper(name), 'B' as which
       from CMS.dbo.TABLE_B
      )
     ) ab
where not exists (select 1
                  from CMS.dbo.TABLE_C_EXCEPTION e
                  where upper(e.name) = ab.name
                 )
having count(distinct which) <> 2;

SQL Server is case-insensitive by default. I left the upper()s in the query in case your installation is case sensitive.

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

Here is another option using EXCEPT. I added a group by in each half of the union because it was not clear in your original post if DB_NAME is unique in your tables. 

select DatabaseName
from
(
    SELECT UPPER(ta.DB_NAME) AS DatabaseName
    FROM [CMS].[dbo].[TABLE_A] AS ta
    GROUP BY UPPER(ta.DB_NAME)

    UNION ALL

    SELECT UPPER(tb.DB_NAME) AS DatabaseName
    FROM [CMS].[dbo].[TABLE_B] as tb
    GROUP BY UPPER(tb.DB_NAME) 
) x
group by DatabaseName
having count(*) < 2
EXCEPT
(
    select DN_Name 
    from CMS.dbo.TABLE_C_EXCEPTION
)

Upvotes: 0

Related Questions