Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tmcivery
Starting Member
46 Posts |
 Posted - 2006-08-14 : 14:05:15
|
| Hey all,Here is the scenario. I'm working with two tables:Contact1ConthistContact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record.The link between the two tables is a column called accountno.I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno.Any help would be appreciated.Thanks,Tony |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 14:09:59
|
| DELETE chFROM Conthist chLEFT OUTER JOIN Contact1 caON ch.accountno = ca.accountnoWHERE ch.accountno IS NULLRun this to ensure it is what you want though:SELECT ch.*FROM Conthist chLEFT OUTER JOIN Contact1 caON ch.accountno = ca.accountnoWHERE ch.accountno IS NULLTara Kizer |
 |
|
|
tmcivery
Starting Member
46 Posts |
Posted - 2006-08-14 : 14:17:51
|
| Tara,This is returning nada for me. Could it be the IS NULL statement as the conthist accountno field doesn't actually hold a NULL value even if the field is empty which it is in some cases.Thanks,Tony |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 14:19:48
|
| Oops. It shold be WHERE ca.accountno IS NULL.The NULL gets placed in there due to the LEFT OUTER JOIN. So it doesn't exist in your actual data. It is just showing you where the right table doesn't have any matches in the join.Tara Kizer |
|
|
|
tmcivery
Starting Member
46 Posts |
Posted - 2006-08-14 : 14:24:43
|
| Tara,That did it. I appreciate your help! Tony |
|
|
|
|
|
|
Deleting records