Where clause in Union

Transact-SQL
1

Hello,
I have the following query but the WHERE clause is not working:

SELECT ROW_NUMBER() OVER(ORDER BY [ID]) RowNumber,
Which = 'New Value',
*
FROM (
SELECT * FROM dbo.NewData
EXCEPT
SELECT * FROM dbo.Master
) X
UNION ALL
SELECT
ROW_NUMBER() OVER(ORDER BY [ID]) RowNumber,
'Old Value',
*
FROM (
SELECT * FROM dboMaster
EXCEPT
SELECT * FROM dbo.NewData
)Y
ORDER BY RowNumber, Which
WHERE X.ID = Y.ID ---- NOT WORKING

Thanks.

2

YOu should be doing a JOIN, not a union

1 Like
3

How to do join with that code?

4

on second thought, why not just drop the WHERE clause?

5

Dropping Where clause will not work. I have to have ID matching from both sets.

6

OK -- let's backup. What are you trying to achieve?

7

I am comparing 2 tables and showing differences in rows. But if a new row was added in table b then I want to distinctly specify that this was a newly added row.

8

OK -- try something like this. I can't test it since I don't have your data:

SELECT ROW_NUMBER() OVER (
            ORDER BY [ID]
            ) RowNumber
      , x.Which
      , x.*
      , y.Which
      , y.*
FROM (
      SELECT *
      FROM dbo.NewData
      
      EXCEPT
      
      SELECT *
      FROM dbo.Master
      ) X
INNER JOIN (
      SELECT ROW_NUMBER() OVER (
                  ORDER BY [ID]
                  ) RowNumber
            , Which = 'Old Value'
            , *
      FROM (
            SELECT *
            FROM dboMaster
            
            EXCEPT
            
            SELECT *
            FROM dbo.NewData
            )
      ) Y
WHERE X.ID = Y.ID ---- NOT WORKING
ORDER BY x.id
9

This was what I had in my original question, but the parenthesis are off or this cannot work. Try copying this to your ssms. Thanks.

10

Corrected version (simulated the tables):

SELECT x.RowNumber
      , x.Which
      , x.*
      , y.Which
      , y.*
FROM (
      SELECT ROW_NUMBER() OVER (
                  ORDER BY [ID]
                  ) RowNumber
            , Which = 'New Value'
            , *
      FROM (
            SELECT *
            FROM dbo.NewData
            
            EXCEPT
            
            SELECT *
            FROM dbo.Master
            ) X
      ) X
INNER JOIN (
      SELECT ROW_NUMBER() OVER (
                  ORDER BY [ID]
                  ) RowNumber
            , Which = 'Old Value'
            , *
      FROM (
            SELECT *
            FROM dbo.Master
            
            EXCEPT
            
            SELECT *
            FROM dbo.NewData
            ) Y
      ) Y
      ON X.ID = Y.ID ---- NOT WORKING
ORDER BY x.id
11

Hello gbritton,
It is working but I need to explain more. Assume this query gives me 3 records:
SELECT ROW_NUMBER() OVER (
ORDER BY [ID]
) RowNumber
, Which = 'New Value'
, *
FROM (
SELECT *
FROM dbo.NewData

        EXCEPT
        
        SELECT *
        FROM dbo.Master
        ) X

and this query gives me 2 records, because this is an older table:

SELECT ROW_NUMBER() OVER (
ORDER BY [ID]
) RowNumber
, Which = 'Old Value'
, *
FROM (
SELECT *
FROM dbo.Master

        EXCEPT
        
        SELECT *
        FROM dbo.NewData
        ) Y

In the result I need to see 4 records, 2 from each table that matches the records. Thanks.

12

So need to write UNION

13

No -- you don't need that nor do you need a union. The join gives you old and new in one row. Make it a full outer join to catch the cases where there is no match and filter for non-null rows.

14

Full outer join only gives me 3 records from 1 table

15

ok -- and each row has old and new. right?

16

That is the problem. Each row only has new value.

17

ok. time to post some sample data and expected results

18

I set up like this:

create table dbo.CurData (id int, a char)
create table dbo.NewData (id int, a char)
create table dbo.OldData (id int, a char)

insert into CurData(id, a) values
   (1, 'a'),
   (2, 'b')

insert into NewData(id, a) Values
   (1, 'c'),
   (2, 'd')

insert into OldData(id, a) Values
    (1, 'e'),
    (2, 'f')

With the query I posted, I got:

RowNumber	Which	        RowNumber	Which	       id	a	Which	        RowNumber	Which	       id	a
1 	        New Value	1	        New Value	1	c	Old Value	1	        Old Value	1	a
2	        New Value	2	        New Value	2	d	Old Value	2	        Old Value	2	b
19

In the result set I also need the old values. Thanks.

20

They are there