Finding Differences Between Two Tables - Why is This Failing?

[txdave35]

Hi everyone,

I am trying to write a query that will compare two tables and report the differences. The differences can occur on two levels, screen differences with is a field called USER_TRAN_CD and individual permissions are stored in the fields user_tran_cd, user_inq_cd, user_add_cd, user_chg_cd, user_dlt_cd, user_q_cd.

I keep hitting a brick wall. I can't even get the query to tell only report which screens are occuring in Michele table but not Allison. I tried this query:

select * from michele

where not exists


(SELECT   * 
FROM     michele a, 
         ALLISON b 
WHERE    a.USER_TRAN_CD=b.USER_TRAN_CD)

But nothing is returned. Looks like it should to me. I'm telling it to only show me user_tran_cds that are not existing in the sub query. What am I missing?

 

[gmmastros]

Try:

Select *
From   Michele
       Full Join Allison
         On Michele.USER_TRAN_CD = Allison.USER_TRAN_CD
Where  Michele.USER_TRAN_CD Is NULL
       Or
       Allison.USER_TRAN_CD

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SaltyTheFrog]

Looks like you have your answer but just for grins here is how I compare two tables. Just another way to skin a cat:

create table ##Old(Column1 char(4), Column2 Char(4))
insert into ##Old(column1, column2) values('aaaa','bbbb')
insert into ##Old(column1, column2) values('aaaa','bbbb')
insert into ##Old(column1, column2) values('aaaa','bbbc') 

create table ##New(Column1 char(4), Column2 Char(4))
insert into ##New(column1, column2) values('aaaa','bbbb')
insert into ##New(column1, column2) values('aaaa','bbbb')
insert into ##New(column1, column2) values('aaaa','bbbd')

 

(SELECT 'In Old Not in New',* FROM ##Old
EXCEPT
SELECT 'In Old Not in New',* FROM ##New)
UNION ALL
(SELECT 'In New Not in Old',* FROM ##New
EXCEPT
SELECT 'In New Not in Old',* FROM ##Old)