title: Simplify my mysql query
body: <p>I currently have the following tables with:</p>
  
  <pre><code>TABLE klusbonnen_deelnemers:
  bonnummer (varchar) - order number
  adres (varchar) - order adres
  deelnemer (varchar) - user
  binnen (date) - date order received
  klaar (date) - original order milestone
  datum_gereed (date) - date order completed
  gereed (varchar) - YES or NO (YES= completed NO= Not yet completed)
  datum_factuur (date) - date when user marked order completed (button clicked)
  factuur (varchar) - weeknumber order completed
  </code></pre>
  
  <p>One order(bonnummer) can have multiple users (deelnemer) who all have to mark the order "completed" (datum_gereed). Only when ALL users (deelnemer) have marked an order (bonnummer) "completed" (datum_gereed) the order IS "completed".
  I am trying to write a query that gives me:
  All completed orders (bonnummer) in a given timespan (last month).
  However...
  The completion date (datum_gereed) should hold the LAST date (as that is the actual total completion date).
  The list should have the Order (bonnummer) with the latest "marked completed" date (datum_factuur) on top (sort DESC) (of course only when all users (deelnemer) have completed the order (all users(deelnemers) having gereed="YES")</p>
  
  <p>So far i have this:</p>
  
  <pre><code>SELECT  DISTINCT tbl1.bonnummer AS 'KLUSBONNUMMER', tbl1.adres AS 'ADRES',
          tbl1.binnen AS 'BINNENGEKOMEN OP', tbl1.klaar AS 'ORIGINELE STREEFDATUM',
          tbl1.datum_gereed AS 'GEREEDGEKOMEN OP', tbl1.factuur AS 'WEEKNUMMER'
      FROM  klusbonnen_deelnemers AS tbl1
      INNER JOIN  
        ( SELECT  tbl2.bonnummer
              FROM  klusbonnen_deelnemers AS tbl2
              WHERE  tbl2.bonnummer NOT IN (
                  SELECT  tbl3.bonnummer
                      FROM  klusbonnen_deelnemers AS tbl3
                      WHERE  tbl3.gereed = 'NEE')
        ) AS tbl4 ON tbl1.bonnummer = tbl4.bonnummer
      INNER JOIN  
        ( SELECT  bonnummer, MAX(datum_gereed) AS 'MAXDATUM'
              FROM  klusbonnen_deelnemers
              GROUP BY  bonnummer
        ) MAXFILTER ON tbl1.bonnummer = MAXFILTER.bonnummer
        AND  tbl1.datum_gereed = MAXFILTER.MAXDATUM
      WHERE  tbl1.datum_factuur BETWEEN NOW() - INTERVAL 2 MONTH AND NOW()
      ORDER BY  tbl1.bonnummer DESC
  </code></pre>
  
  <p>This query DOES work, however i think this can be done in a much simpler way.
  On top of that the query only works in my navicat editor. Calling this query on my "live" website gives an error (subquery in WHERE clause...) (i do have all login correct as other queries DO work).</p>
  
  <p>Anyone out there who can help (simplify) this query? Thx...</p>