Simple SQL match question 1

[Mbroad]

Hi-I am a newbie to SQL, so please bear with me. I am trying to run a SQL server statement which will match DB1 against DB2 finding only the NON MATCHES between the two (and writing to a third DB).
DB1 has azpprox 600,000 rows in it, DB2 has 160,000 rows.
This is code I am using, but it is only finding 1 non match (and writes a NULL row in 3rd DB).
See what you think:

SELECT DISTINCT FIN.NAME, FIN.ADDRESS, FIN.CITY,
FIN.STATE, FIN.ZIP, FIN.ZIP4, FIN.DATEOFBIRTH,
FIN.SEX, FIN.SID

into SOS_41_RERUN

FROM SOS_TABLE KEYN left outer join FINAL_FILE FIN

on FIN.SID = KEYN.SID
WHERE FIN.SID is null;

I truly appreciate your help!!
GCM

Government Coder Mark

 

[SQLSister]

The reason why the select into is giving you null records is because you are using fields from the table where you specified there would be no records in the left join.

I would guess your problem is that you need to switch the join
FINAL_FILE FIN left outer join SOS_TABLE KEYN
on FIN.SID = KEYN.SID
WHERE KEYN.SID is null

BTW don't use the ; at the end it is unnecessary in SQL Server


"NOTHING is more important in a database than integrity." ESquared

 

[Mbroad]

Sorry SQLsister, I tried the code just as you said and still only get one NULL record in return. Like I said, DB1 has 600,000 rows and DB2 has 160,000 rows and most/all of DB2 rows should be in DB1---I only want the rows which are not.

Thanks a lot,
GCM

Government Coder Mark

 

[gmmastros]

Try removing the DISTINCT keyword at the beginning of your query.


-George

"the screen with the little boxes in the window." - Moron

 

[Mbroad]

gmmastros-Sorry, but removing the DISTINCT did not do it either---I must be missing something very elementary here--
Just want to find the rows which are on table 1 and are not on table 2--that's all

Any thoughts?

GCM

Government Coder Mark

 

[gmmastros]

Mark,

Can you please re-state your question. But this time, do not reference DB1 and DB2, or Table 1 and Table 2. The query you show has an SOS_TABLE table and a FINAL_FILE table. Which table has more records than the other?

-George

"the screen with the little boxes in the window." - Moron

 

[Mbroad]

Sure--sorry about that.
My SOS_TABLE has 640,000 rows in it.
My FINAL_FILE has 160,000 rows in it.
All rows from SOS_TABLE should be present on FINAL_FILE table.

I want to find all rows on SOS_TABLE which are NOT in FINAL_FILE.

The above query only returns 1 NULL record.

Please help!
Thanks,
GCM

Government Coder Mark

 

[gmmastros]

The problem is...

You are trying to identify records that exist in SOS_Table but not in FINAL_FILE, but you are selecting the columns from FINAL_FILE. Instead, you should be selecting the columns from the SOS_TABLE.

SELECT DISTINCT 
       KEYN.NAME,  
       KEYN.ADDRESS, 
       KEYN.CITY,
       KEYN.STATE, 
       KEYN.ZIP, 
       KEYN.ZIP4, 
       KEYN.DATEOFBIRTH,
       KEYN.SEX, 
       KEYN.SID
FROM   SOS_TABLE KEYN 
       left outer join FINAL_FILE FIN
         on FIN.SID = KEYN.SID
WHERE  FIN.SID is null;

-George

"the screen with the little boxes in the window." - Moron

 

[Mbroad]

Thank you!! Thankyou!! That worked superbly!! Apreciate much your assistance!!


Government Coder Mark