Temp Table Comparison SQL

Question

Post reply

Temp Table Comparison SQL

Rog Saber

SSCrazy

Points: 2548
More actions
December 5, 2007 at 10:28 pm

#179753

I have a proc and I'm selecting data into
Temp table A (col1,col2,col3,col4) that meets certain conditions.
Then, I'm selecting data into
Temp table B (col1,col2,col3,col4) that meets certain conditions.
What I would like to do is then select * from Temp table A that's not in Temp table B. Then I want to select everything from Temp table B that's not in Temp table A.
I'm just not sure of the where clause.
Example:
Temp table A
col1...col2...col3...col4
joe....table..saw...yellow
bob....table..saw...green
phil....table..saw...yellow
Temp table B
col1...col2...col3...col4
joe....table..saw...yellow
dan....table..saw...yellow
First result set from select
bob....table..saw...green
phil....table..saw...yellow
Second result set from select
dan....table..saw...yellow

Viewing 5 posts - 1 through 4 (of 4 total)

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

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

SELECT * FROM TempTableA A

WHERE NOT EXISTS

(SELECT * FROM TempTableB Z WHERE Z.col1 = A.col1 AND Z.col2 = A.col2 AND Z.col3 = A.col3 AND Z.col4 = A.col4)

Avaneesh -388582 SSChasing Mays Points: 657 More actions · Answer 2

Hi,

you can try out the following queries:

select * from A where A.col1 not in

(select A.Col1, from Table A, Table B where A.col1=B.col1)-- This will fetch common results in both the tables

select * from B where B.col1 not in

(select A.Col1, from Table A, Table B where A.col1=B.col1)

Hope that helps..:)

Regards,

Avaneesh Bajoria.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

How about all having in a single query...

SELECTA.*, B.*

FROMTempTableA A

FULL OUTER JOIN TempTableB B ON A.col1 = B.col1 AND A.col2 = B.col2 AND A.col3 = B.col3 AND A.col4 = B.col4

--Ramesh

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Ya beat me to it, Ramesh!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)