CODEWITHSUNDEEP
sql-server-2008-r2
BrianKE
BrianKE

Reputation: 4185

SQL EXCEPT ignore some columns during compare

Assume I have the following tables:

##TABLE1

FirstName, LastName,     AsOfDate, NoOfEpisodes
     Bart,  Simpson,   07/01/2018,          123
     Lisa,  Simpson,   03/01/2018,          110

##TABLE2

FirstName, LastName,     AsOfDate, NoOfEpisodes
     Bart,  Simpson,   08/01/2018,          123
     Lisa,  Simpson,   08/01/2018,          130

If I run the following SQL command to find the differences between the two tables:

select * from ##TABLE2 
except (select * from ##TABLE1)

The result would be 

  FirstName, LastName,     AsOfDate, NoOfEpisodes
       Bart,  Simpson,   08/01/2018,          123
       Lisa,  Simpson,   08/01/2018,          130

I would like to run this comparison but ignore the AsOfDate field yet keep it in the final results. If I do this SQL:

select FirstName, LastName, NoOfEpisodes
from ##TABLE2 
except (select FirstName, LastName, NoOfEpisodes from ##TABLE1)

the results would look like this 

  FirstName, LastName,  NoOfEpisodes
       Lisa,  Simpson,           130

but I need it to look like this 

FirstName, LastName,     AsOfDate, NoOfEpisodes
     Lisa,  Simpson,   08/01/2018,          130

Obviously this is a sample set of data. The data in production may contain 20-30 columns and I would only want to exclude a few at most. I was hoping there might be something like an 'IGNORE' or 'NOT IN' function that I could apply to the SQL script to exclude just a couple of columns.

Upvotes: 2

Views: 2620

Answers (1)

sean
sean

Reputation: 1205

What about?

SELECT T1.FirstName, T1.LastName, T2.AsOfDate, T2.NoOfEpisodes
FROM ##TABLE1 T1
INNER JOIN ##TABLE2 T2 
     ON T1.FirstName = T2.FirstName AND T1.LastName = T2.LastName
WHERE T1.NoOfEpisodes != T2.NoOfEpisodes

Ok, per your comment. What if we go with a CTE?

;WITH ex as (
   select FirstName, LastName, NoOfEpisodes
   from ##TABLE2 
   except (select FirstName, LastName, NoOfEpisodes from ##TABLE1)
)
SELECT ex.FistName, ex.LastName, ex.NoOfEpisodes, T2.AsOfDate
FROM ex
INNER JOIN ##TABLE2 T2
   ON ex.FirstName = T2.FirstName AND ex.LastName = T2.LastName

If the columns are getting too many to deal with, you can use excel to write the SQL where clause for you. Open SSMS and "Select top 1000 Rows" which will give you a list of the columns for the table. Copy that list and paste it into excel's A column. Then in the B column, add this function: 

="OR T1." & A1 & " != T2." & A1

Copy that function to all rows in B and then you can copy the values which will be a long list of conditions to put in the where clause. You can to the same this with the SELECT list:

="T2." & A144 & ", "

Upvotes: 1

Related Questions