title: MYSQL - How to get missing records in an unorganized table
body: <p>I have the following table in MYSQL which is not organized in the best way, but I have to handle it like that.</p>
  <p>details</p>
  <pre><code>id INTEGER PRIMARY KEY
  plant_id INTEGER FOREIGN KEY
  state VARCHAR
  county VARCHAR
  municipality VARCHAR
  count INTEGER
  </code></pre>
  <p>Each record contains the count of how many specimens of a particular plant are present in a state, or in a county, or in a municipality.</p>
  <p>Specimen count records for a plant in a state have the county and municipality fields equal to NULL.
  Plant count records in a county have the state field which contains the name of the state it belongs to (there is no foreign key) and the municipality field equal to NULL.
  The specimen count records of a plant in a municipality have the state and county fields containing their respective names (without foreign key).</p>
  <p>I should check which counties I have the total count of but I don't have any municipal count records. I had thought of a LEFT OUTER JOIN created in the following way, left table with counties count, right table with municipalities count, but unfortunately it doesn't work.</p>
  <pre><code>SELECT d1.*, d2.*
  FROM details AS d1 
  LEFT OUTER JOIN details AS d2 
  ON (d1.id != d2.id 
      AND d1.plant_id = d2.plant_id 
      AND d1.county = d2.county 
      AND d1.county IS NOT NULL 
      AND d1.municipality IS NULL
      AND d2.municipality IS NOT NULL
     )
  WHERE d2.id IS NULL
  </code></pre>
  <p>Thanks</p>