Re: SQL-Find missing data

>'m writing an integrity checker for our system at work which
>basically
>scans the database tables for any discrepant pieces of data
>that should
>not be there. Anyway, there's one rule which is as follows :

Why don't you use a trigger to determine if the data is correct? That way, you never have to go back and check the data.

>For all names in table_a whose status is 'P', a record should
>exist in
>table_b. I want a list of all names and corresponding status
>which DO

But you can try this:

  SELECT a.name, a.status, b.status
  FROM table_a a, table_b b
  WHERE a.name = b.name AND a.status!=b.status;

This will join the two tables but only for those records that have unequal status.

Hope that helps!
Brian Peasland
peasland_at_msn.com Received on Sat Jun 19 1999 - 18:22:55 CDT