CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-proceduressql-server-2008-r2
kyusan93
kyusan93

Reputation: 422

Data comparison issue

I have the below sql query:

select @table1 as DataState, *  from
(select * from importedcsvclients 
except 
select * from tblClients) x
union all
select @table2 as DataState, *  from
(select * from tblClients 
except select *  
from importedcsvclients) x

The above code works fine, however, if table1 and table2 consist similar data, both records will show.

Can anyone assist me to make the query work as:

get the results of both table1 and table2, but only show table2 data if the same name doesn't exist in table1.

Thanks.

For Info:

table1
ID   Name
1    TestA
2    TestB    
3    TestC
4    TestD

table2
ID   Name
1    TestE
2    TestF    
3    TestG
4    TestD

Results:
Name     DataState
TestA    table1
TestB    table1
TestC    table1
TestD    table1
TestE    table2
TestF    table2
TestG    table2

Upvotes: 1

Views: 81

Answers (3)

kyusan93
kyusan93

Reputation: 422

I used the below now. Thanks for all assistance.

DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT ''' + @table1 + ''' AS DataState, * FROM
(SELECT ' + @columnlist + ' FROM ' + @table1 + '
    EXCEPT
SELECT ' + @columnlist + ' FROM ' + @table2 + ') x
    UNION
SELECT ''' + @table2 + ''' AS DataState, * from
    (SELECT ' + @columnlist + ' FROM ' + @table2 + '
        INTERSECT
    SELECT ' + @columnlist + ' FROM ' + @table1 +') x'

EXEC(@sql)

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

In (pesudo) relational algebra:

table1 UNION ( table2 NOT MATCH table1 )

In TSQL (adding DataState):

SELECT ID, Name, 
       'table1' AS DataState
  FROM table1
UNION
SELECT ID, Name,
       'table2' AS DataState
  FROM table2
 WHERE Name NOT IN ( SELECT Name FROM table1 );

Including your sample data test data:

WITH table1 
     AS
     (
      SELECT * 
        FROM (
              VALUES (1, 'TestA'),
                     (2, 'TestB'),    
                     (3, 'TestC'),
                     (4, 'TestD')
             ) AS T (ID, Name)
     ),
     table2
     AS
     (
      SELECT * 
        FROM (
              VALUES (1, 'TestE'),
                     (2, 'TestF'),    
                     (3, 'TestG'),
                     (4, 'TestD')
             ) AS T (ID, Name)
     )
SELECT ID, Name, 
       'table1' AS DataState
  FROM table1
UNION
SELECT ID, Name,
       'table2' AS DataState
  FROM table2
 WHERE Name NOT IN ( SELECT Name FROM table1 );

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Very tempting to use EXCEPT but as you've found it is not always as straightforward to get the proper results from anything with a little complexity. Here's a try at the result you're after using a CTE:

DECLARE @table1 TABLE(ID INT, Name VARCHAR(32));    
INSERT @table1 VALUES (1,'TestA'), (2,'TestB'), (3,'TestC'), (4,'TestD');

DECLARE @table2 TABLE(ID INT, Name VARCHAR(32));
INSERT @table2 VALUES (1,'TestE'), (2,'TestF'), (3,'TestG'), (4,'TestD');

;WITH x AS 
(
  SELECT m, Name, rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY m)
  FROM 
  (
    SELECT m = 'table1', Name FROM @table1
    UNION ALL 
    SELECT m = 'table2', Name FROM @table2
  ) AS y
)
SELECT Name, DataState = m
  FROM x 
  WHERE rn = 1
  ORDER BY Name;

Results:

Name        DataState
---------   ---------
TestA       table1
TestB       table1
TestC       table1
TestD       table1
TestE       table2
TestF       table2
TestG       table2

Upvotes: 3

Related Questions