values that do not match

Question

Post reply

values that do not match

pzmrcd

Default port

Points: 1419
More actions
August 3, 2005 at 3:39 pm

#90298

Hi
I have table1 with a column "id" and
table2 with a column "id". The id column is supposed to be the same in both the tables...but does not.
I am trying to write a query that will list the id's (records) from both the tables that do not match. Can someone help me with this query.
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247037 More actions · Answer 1

select 'in table1 not table2' Type, t1.id from table1 t1 where t1.id not in (select t2.id from table2 t2)

union

select 'in table2 not table1' Type, t3.id from table2 t3 where t3.id not in (select t4.id from table1 t4)

order by 1

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Wouldn't it be faster to just do a full outer join where either id is null??

noeld SSC Guru Points: 96590 More actions · Answer 3

>>Wouldn't it be faster to just do a full outer join where either id is null?? <<

YES:

select (case when t2.id is null then 'in table1 not table2' else 'in table2 not table1' end) Type

, coalesce(t1.id,t2.id) TheID

from

table1 t1 full outer join table2 t2 on t1.id =t2.id

where

t1.id is Null or t2.id is null

order by Type

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Nice to see you back online... been almost 2 days now .

noeld SSC Guru Points: 96590 More actions · Answer 5

Yes, it is nice to be back but probably will have to take a second break on my next two weeks in london (Job stuff!)

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Damn... do you need an assitant, maybe a junior DBA .

Phil Parkin SSC Guru Points: 247037 More actions · Answer 7

Hey, you guys are ganging up on me ... it was late, I was tired & just trying to help

Anyway, my solution requires only one line and does not depend on the ability to spell coalesce

noeld SSC Guru Points: 96590 More actions · Answer 8

hey Phil,

I just posted "another" way, never though of it as "ganging on you"

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

No we're not... just stating that there might be a better way.

BTW I was actually serious about the junior DBA position .

noeld SSC Guru Points: 96590 More actions · Answer 10

BTW I was actually serious about the junior DBA position

Don't worry I am pretty sure you will do well. If something comes up I'll let you know

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

I'm really thinking to upgrade and move to the us... warmer climate funnier job, better conditions (hopefully). Closer to the us pool tour . Where do you work exactly?

noeld SSC Guru Points: 96590 More actions · Answer 12

NY.

Still trying to figure out where sushila works though

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I heard maybe NY or london... but she also said to me tha she lived in China so I have no clue.