asked
compare 2 table records
I have 2 tables each table has 2 count of group by records , I need to compare those records are equal or not . table 1 result set after count(input),group by (input) input count 0 10 1 122 table 2 result set input count 0 10 1 122 I need to compare and see 2 tables has equal count. any thoughts ?
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
select * from (table 1 result set after count(input),group by (input)) as T1 left outer join (table 2 result set) as T2 on T2.input = T1.input and T2.count = T1.count where T2 IS NULL -- the row is missing/not exact match in T2; where T2 IS NOT NULL -- the row is exact match; Keep in mind that the position of the tables in the LEFT JOIN matter! If you need to compare the existence of the rows in both tables equally, then use FULL OUTER JOIN. Is this what you're looking for?
For your example, try this. It assumes that input is a unique value create table #TableA (input int primary key, [count] int) insert into #TableA values (0,10),(1,122),(3,10),(4,122) create table #TableB (input int primary key, [count] int) insert into #TableB values (0,10),(1,122),(3,20),(5,122) Select min(source) as source, input, [count] from (Select 'TableA' as source, input, [count] from #TableA union all Select 'TableB', input, [count] from #TableB)f group by input, [count] having count(*)=1 /* source input count ------ ----------- ----------- TableA 3 10 TableB 3 20 TableA 4 122 TableB 5 122 (4 row(s) affected) */
17 People are following this question.
