Comparing data in two tables

Question

Hello, 

Here is what I am trying to achieve:

- comparing a single column in two tables

- Success = all fields in table 1 must exist in table 2.  There may, or may not,  be additional fields in table 2.

Here is my test code.   Is this the best way to do this, or is there a better way?

create table #t1 (fname varchar(20));
create table #t2 (fname varchar(20));

insert into #t1 values ('1234INV'),('1234SALES'),('9191INV');
insert into #t2 values ('1234SALES'),('1234INV'),('9191INV'),('5678INV'),('5757INV');

Select 
Case When Count(t1.Fname) = 0 Then 'No Rows'
  When Sum(Case When F.FName Is Null Then 1 Else 0 End) = 0 Then 'Success'
  Else 'Fail'
  End
From #t2 F Full Join #t1 t1 On F.Fname = t1.Fname;
	
drop table #t1;drop table #t2;

Thank you for your suggestions,

Tom

---

MisterT99

Answer 1

Try  Intersect:

create table #t1 (fname varchar(20));
create table #t2 (fname varchar(20));

insert into #t1 values ('1234INV'),('1234SALES'),('9191INV');
insert into #t2 values ('1234SALES'),('1234INV'),('9191INV'),('5678INV'),('5757INV');

 

Select fname from  #t1
Intersect
Select fname from  #t2


Select fname from  #t1
Except
Select fname from  #t2

Select fname from  #t2
Except
Select fname from  #t1
	
	
drop table #t1;drop table #t2;

Answer 2

DECLARE @t1 table (fname varchar(20));
DECLARE @t2 table (fname varchar(20));

insert into @t1 values ('1234INV'),('1234SALES'),('9191INV');
insert into @t2 values ('1234SALES'),('1234INV'),('9191INV'),('5678INV'),('5757INV');

SELECT fname, count(*), min(tName) AS min, max(tName) AS max
FROM (
SELECT *, 't1' AS tName
  FROM @t1
UNION ALL
SELECT *, 't2' AS tName
  FROM @t2
) a
GROUP BY fname
HAVING COUNT(*) <> 2

This is a nice easy ware to compare the contents of a table to another.

Any records which are not exactly duplicated cause rows to be produced. If the count is 1, the record is only in one of the tables, if the count is more than 2 the record is in one (or both) of the tables multiple times.