Finding missing rows in a multi-table MySQL database

I've just started working on a MySQL database that somebody else put together, and I'm trying to clean it up. There are three relevant tables to my problem - a table listing auctions, a table listing the lots sold at each auction and a table listing the payment schedule for each lot.

The auctions table uses a primary key to identify each auction, and each lot has this key to identify it with the auction. Likewise, each lot has a different key, which each payment uses to identify it w开发者_StackOverflowith the lot. Each auction should have at least one lot and each lot should have at least one payment. No payment should be attached to a lot which doesn't have a row in the lots table, and no lot should be attached to an auction which doesn't have a row in the auctions table. I'm trying to work out a query that will allow me to find the rows that break this rule - for example missing lots in the lots table or extra payments which don't have a lot attached.

At the moment I'm doing a count distinct query which gives me the number of lots/auctions mentioned in the relevant tables, but if the numbers don't match it doesn't help me find which ones are missing. Is there a query that I can use to find the rows that are missing?

Thanks

You could check for the keys in the related tables.

Select a.* from Auction a
Left Join Lot l on l.key = a.key
where l.key is null

or

select * from Auction a
where a.key not in (select key from lot)

something like these will help you. you'll need to change the table and column names to match and then script some for the other appropriate cases but the logic should be simmilar. hope this helps.

Something like,

SELECT lot.pk From lot WHERE lot.fk NOT IN(SELECT DISTINCT(auction.pk) FROM auction)

would give you the lots attached to non existant auctions, amend as required for your other situations.