title: How to compare a date from outer query with date in inner query using MYSQL?
body: <p>I have customers, jobs, orders and dates as columns. Customers can have multiple orders per job and of course multiple jobs given a certain date range.
  <br>
  What I am trying to do is figure out how many &quot;new&quot; orders there are within a certain date range. Meaning, if a job has 5 orders, it should only return 1 order.
  Here's a short example of a database table, desired result, and the query I have been trying to work with thus far.</p>
  <pre><code>+-------+-------+---------+------------+
  | CusID | JobID | OrderNo |  OrderDate |
  +-------+-------+---------+------------+
  |   1   |   10  |    25   | 2021-12-22 |
  |   1   |   10  |    26   | 2022-02-09 |
  |   3   |   5   |    28   | 2022-01-10 |
  |   3   |   6   |    29   | 2022-01-11 |
  +-------+-------+---------+------------+
  </code></pre>
  <p>There are 4 rows of orders with their associated <code>JobID</code>. The query should return 2 rows since order 25 and order 26 are both associated with <code>JobID</code> 10, only 1 can be counted as &quot;new&quot;, while <code>OrderNo</code> 25 falls outside of the outer queries date range.
  <br>
  Here is the query I am trying to use, based on the date being <code>&gt;=</code> to the first of the year. I want to be able to use the <code>OrderDate</code> from the outer query, to compare with the <code>OrderDate</code> from the inner query. If there is a <code>JobID</code> with an <code>OrderDate</code> that is less than the current rows <code>OrderDate</code> that order is NOT new and should return the <code>JobID</code>, thus making the inner query false and filtering out the row.</p>
  <pre><code>SELECT * FROM orders
  WHERE OrderDate &gt;= '2022-01-01' 
  AND JobID NOT IN 
  (SELECT JobID FROM orders WHERE inner.OrderDate &lt; outer.OrderDate)
  </code></pre>
  <p>Expected result</p>
  <pre><code>|   3   |   5   |    28   | 2022-01-10 |
  |   3   |   6   |    29   | 2022-01-11 |
  </code></pre>