SQLTeam.com | Weblogs | Forums

Where clause in Union


#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


#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