vbnetcoder
asked on
Compare two tables
I have two tables: TableA & TableB
TableA has a primary key that corresponds to a foreign key in TableB.
How would i write a query to check if there are foreign key values in TableB that do not have a corresponding primary key value in TableA?
TableA has a primary key that corresponds to a foreign key in TableB.
How would i write a query to check if there are foreign key values in TableB that do not have a corresponding primary key value in TableA?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@appari solution should work. You may also achieve this using a LEFT OUTER JOIN like so:
SELECT DISTINCT B.FKColumn1, B.FKColumn2, ...., B.FKColumnN
FROM TableB AS B
LEFT OUTER JOIN TableA AS A ON A.PKColumn1 = B.FKColumn1, A.PKColumn2 = B.FKColumn2, ..., A.PKColumnN = B.FKColumnN
WHERE A.PKColumn1 IS NULL
select FK from tableB
except
select PK from tableA
except
select PK from tableA
exec sp_help 'TableA'
Any foreign key relationships will display in the 'Table is referenced by foreign key' recordset