help on writing a Query to delete records

[cfbeginner]

Hi All,

I have a 3 related tables,

ContactTable:
(PK)COntactID, (FK)CompanyRepID, ContactName, CompanyRepID, Address etc...

CompanyRepTable:
(PK)CompanyRepID, (FK)CompanyID, Location, etc..

CompanyTable:
(PK)CompanyID, account, companyname, etc


I need to do two things
1. remove from all related rows in CompanyReptable that is associated with a contact.

2. remove from CompanyTable that does not have a associated companyrep

Thanks

 

[StevenK]

I think the following (maybe someone else can back me up ...)

1. To delete entries from CompanyRepTable that have an associated contact :
DELETE FROM CompanyRepTable
WHERE CompanyRepID IN
(SELECT DISTINCT CompanyRepID FROM ContactTable)

2. To delete entries from CompanyTable where there is no associated CompanyRepTable entry :
DELETE FROM CompanyTable
WHERE CompanyID NOT IN
(SELECT DISTINCT CompanyID FROM CompanyRepTable)

Best take a backup of the database first to be on the safe side ... ;-)

Steve