title: Retrieve all records if no record exist in the joining table
body: <p>I have these three tables,</p>
  <p>PO_HEADER</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>po_number</th>
  <th>vendor_name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>10001</td>
  <td>Xyz</td>
  </tr>
  <tr>
  <td>100002</td>
  <td>ABC</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>PO_LINEITEM</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>line_number</th>
  <th>po_number</th>
  <th>item_quantity</th>
  <th>recieved_quantity</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0001</td>
  <td>10001</td>
  <td>20</td>
  <td>10</td>
  </tr>
  <tr>
  <td>0002</td>
  <td>10001</td>
  <td>80</td>
  <td>10</td>
  </tr>
  <tr>
  <td>0002</td>
  <td>10002</td>
  <td>40</td>
  <td>35</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>SHIPMENT_LINEITEM</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>line_number</th>
  <th>po_number</th>
  <th>shipping_quant</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0001</td>
  <td>10001</td>
  <td>20</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How to retrieve order_master based on these two conditions</p>
  <ol>
  <li>if any po_lineitem having item_quantity &gt; recieved quantity.</li>
  <li>if po_lineitem having item_quantity &gt; (sum of previously shippingquantity from asn_lineitem for same line_number &amp; poNumber) .2(b) if there no items in the asn_lineitem this should return true.</li>
  </ol>
  <p>I wrote this query but was not able to achieve 2(b)</p>
  <pre><code>select count(*) from erp_po_header 
  where po_number in
  (select erlitms.po_number from erp_po_line_items erlitms
  JOIN
     (select sum(shipping_quantity) as total_prev_shipped_items,po_number,item_number from 
    asn_lineitem
    Group by po_number,item_number) 
     asn_items 
   ON erlitms.po_number = asn_items.po_number
  and erlitms.item_number = asn_items.item_number
  and erlitms.item_quantity &gt; CAST (erlitms.received_quantity AS INTEGER)
  and erlitms.item_quantity &gt; asn_items.total_prev_shipped_items )   
  </code></pre>