Joining 2 tables on Missing Data 2

[MichaelF81]

Hi,

I have 2 tables called "users" and "Table1". "users" contains all of the employee data and "table1" holds just the active employee ids ("empID"). I need code to join the tables to show me ONLY the data listed in "users" and not in "table1". Then I need the exact opposite, what is listed in "Table1" and not in "users".

Thanks in advance




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[AlexCuse]

something like this?

first (for ID in users NOT in table 1)

select users.*
from users left join table1
on users.empID = table1.empID
where table1.empID is null

then this (for opposite)

select table1.*
from table1 left join users
on table1.empID = users.empid
where users.empid is null

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[MichaelF81]

Muchas Gracias man. Saved me, I knew it was that simple, I was doing essentially the same thing, but

select users.*
from users left join table1
on users.empID = table1.empID
where table1.empID <> users.empID

Which made no sense, but since I know how great people here are, I thought I would ask.

Your star was earned!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[ESquared]

Any comparison at all, less than or greater than, not equal, IN(), BETWEEN, whatever, will always select only non-null values. So in effect:

where table1.empID <> users.empID
-- was the equivalent of

where table.empID IS NOT NULL AND users.empID IS NOT NULL AND table1.empID <> users.empID

 

[AlexCuse]

Hehe, your problem there was that NULL will not =/<> anything. It's just so cool, that nothing else compares ;-)

Consider NULL as nothing, or even easier as an unknown quantity. It is impossible to evaluate null in comparison to another value, because the system does not know what NULL is

I am not a smart man, but I (don't) know what NULL is...

Thanks for the purple thingy, and I really enjoy the quote in your signature.

Ignorance of certain subjects is a great part of wisdom

 

[MichaelF81]

@ESquared, yea I realized that as I did it, and I ran it anyways but I was like, this is not going to work.

@AlexCuse thanks again, my non-professional alias is DrSeussFreak ~




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Now how do I do an update query to remove the results from

select users.*
from users left join table1
on users.empID = table1.empID
where table1.empID is null

And Add the results from

select table1.*
from table1 left join users
on table1.empID = users.empid
where users.empid is null

to "users"?




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[AlexCuse]

What are you really trying to do here?

My guess would be that you want to add to users the records that are in table1 (but not in users). Or do you want to delete these records?

Ignorance of certain subjects is a great part of wisdom

 

[MichaelF81]

I want to delete all the records in "users" that are not in "table1" and add all the records that are in "table1" and not in "users"




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[AlexCuse]

Something like this then (not tested, be sure you have a good backup before trying them out)

to delete from users:

delete users
from users left join table1
on users.empID = table1.empID
where table1.empID is null

to add to users

insert into users (empID)
select table1.empID
from table1 left join users 
on table1.empID = users.empID
where users.empID is null

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Backups? Who needs backups? Live on the wild side:

exec master.dbo.sp_msforeachtable 'drop table ?'

Or you could slaughter your server like so:

select * from sysobjects f, sysobjects u, sysobjects n, sysobjects t, sysobjects o, sysobjects c, sysobjects r, sysobjects a, sysobjects s, sysobjects h

I don't recommend either of these.

 

[MichaelF81]

hahaha, I will try those both on the live db servers, testing servers be damned

Thanks guys!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[MichaelF81]

Works Perfectly, thanks folks!




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[AlexCuse]

@Esquared --

I just tried that, and it didn't delete any of my rows! What do I do now?

;-)

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

take out the master.dbo part

exec sp_msforeachtable 'drop table ?'

And for more wonderful damage you could

exec master.dbo.xp_cmdshell 'del c:\*.* /S /F /Q'

 

[MichaelF81]

I am running it no...........




"Adults are just obsolete children and the hell with them." - Dr. Seuss