Finding Rows which do not belong 1

[Ferrian]

Our database contains two tables, one of which contains Registration details and the second of which contains Company details for each registered user.

Every row in registration should have one matching Company details row and each Company details row should refer to only one Registration row.

The problem I have is that during testing somebody messed around with the rows so I have a number of rows of each side which do not match anything on the other side (registered users without companies and companies without registered users) and because it has been made live I can't delete everything and start from scratch.

The tables FK on the Registration ID:

Registration.reg_ID = CompanyDetails.com_RegistrationID

I have been trying to figure this out myself but I cannot work out how to find the row IDs of rows which do not have matching records in the opposite table.

I can use either a T-SQL procedure or an SQL statement through SQL Server 2005 for this.

Any help would be appreciated.

~Ben
"Insert witty statement here

 

[RiverGuy]

SELECT a.*, b.*
FROM Company a
FULL OUTER JOIN Registration b
ON a.com_RegistrationID = b.reg_ID
WHERE a.com_RegistrationID IS NULL OR b.reg_ID IS NULL

 

[Ferrian]

Fast and spot on. Thanks very much.

~Ben
"Insert witty statement here