I am stuck with the below sql code please help

Transact-SQL
1

Hi,
I am stuck with SQL code I need help.

Dataset1
TableName RowCount

Table-1 12
Table-2 15
Table-3 18

Dataset2
TableName RowCount

Table-1 12
Table-6 15
Table-3 10

Result
TableName Ds1_RowCount Ds2_RowCount

Table-2 15 Null
Table-3 18 10
Table-6 Null 15

I need output like the result table.

  • RowCount columns matched in both tables that record should not display.
2 
SELECT ISNULL(d1.TableName,d2.TableName) as TableName,
      d1.[RowCount],
      d2.[RowCount]
FROM Dataset1 as d1
        FULL JOIN Dataset2 as d2
              ON d1.TableName = d2.TableName
WHERE
    ISNULL(d1.[RowCount],0) <> ISNULL(d2.[RowCount],0);
TableName RowCount RowCount
Table2 15 null
Table3 18 10
Table6 null 15

dbfiddle

1 Like
3

i know the query is solved

just exercising my noodles ... thinking of all different ways to do it

SELECT 
     TableName
   , min(row_count)
   , max(row_count)  
FROM 
	(
		(select * from #Dataset1 except select * from #Dataset2)
			union all 
		(select * from #Dataset2 except select * from #Dataset1)
	)   a
GROUP BY 
	tablename

image

4

Hi,
Thank you so much for your help.

1 Like
5

another way of doing this
not the exact output

create data script

drop table #Dataset1
create table #Dataset1 ( TableName varchar(10) , Row_Count int )
insert into #Dataset1 select 'Table-1', 12
insert into #Dataset1 select 'Table-2', 15
insert into #Dataset1 select 'Table-3', 18
select * from #Dataset1

drop table #Dataset2
create table #Dataset2 ( TableName varchar(10) , Row_Count int )
insert into #Dataset2 select 'Table-1', 12
insert into #Dataset2 select 'Table-6', 15
insert into #Dataset2 select 'Table-3', 10
select * from #Dataset2

SELECT
     tablename
   , row_count
   , count(*)  
FROM
  (
      select * from #Dataset1
         union all 
      select * from #Dataset2
  ) a 
GROUP BY 
       TableName 
	,  row_count 
HAVING 
      count(*) = 1

image