title: How to use mysql subquery with a where clause in a where clause that requires data from the superquery
body: <p>i'm going to try this:</p>
  
  <pre><code>SELECT
      Bestellung.ID BNr,
      RechnAddr Kunde,
      (SELECT
          Name
       FROM `User`
       WHERE ID = Bestellung.SalesPerson) Verkaeufer,
      Clerk Sachbearbeiter,
      (SELECT
           Ort
       FROM `Location`
       WHERE ID = Bestellung.Location) Standort,
      CONCAT_WS('.', LPAD(Day, 2,'0'), LPAD(Month, 2,'0'), Year) Erstelldatum 
  FROM `Bestellung`
  WHERE
      SalesPerson != ''
  AND
      (SELECT COUNT(*) cnt FROM _BestellungsPosition bp
       WHERE bp.OrderID = Bestellung.BNr) = 0
  </code></pre>
  
  <p>i just want all orders that have no order items, but mysql tells me "Unknown column 'Bestellung.BNr' in 'where clause"...
  am i using the subquery wrong?!</p>
  
  <p>please help me</p>
  
  <hr>
  
  <p>edit: i changed the (pseudo) query i used before to the true one, because my pseudo query worked, but the true one with real data didn't</p>
  
  <hr>
  
  <p>edit2: now i changed the query to:</p>
  
  <pre><code>SELECT
      Bestellung.ID BNr,
      RechnAddr Kunde,
      (SELECT
          Name
       FROM `User`
       WHERE ID = Bestellung.SalesPerson) Verkaeufer,
      Clerk Sachbearbeiter,
      (SELECT
           Ort
       FROM `Location`
       WHERE ID = Bestellung.Location) Standort,
      CONCAT_WS('.', LPAD(Day, 2,'0'), LPAD(Month, 2,'0'), Year) Erstelldatum 
  FROM `Bestellung` LEFT JOIN _BestellungsPosition bp
  WHERE
      SalesPerson != ''
  AND
      bp.Order IS NULL
  </code></pre>
  
  <p>and it works... thanks to the responder with this hint, but he deleted his answer i think</p>