title: Finding missing rows in a multi-table MySQL database
body: <p>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.</p>
  
  <p>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 with 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.</p>
  
  <p>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?</p>
  
  <p>Thanks</p>