title: Query to determine if ID exists with certain column value across rows
body: <p>I need to write a query for Purchase Orders and get Header and Line data. This is a one to many relationship, where there is one header row <code>PO_ID</code> (<code>PS_PO_HDR</code>) to one or more associated lines (<code>PS_PO_LINE</code>) via the <code>PO_ID</code> identifier. I need the query to exclude rows where the <code>CANCEL_STATUS</code> is equal to 'X' <strong>UNLESS</strong> that same <code>PO_ID</code> and <code>BUSINESS_UNIT</code> has other rows with values in <code>CANCEL_STATUS</code> other than <code>X'</code>.</p>
  <p>Essentially I need to be able to query to using the other criteria in my query below (other than the specific <code>PO_ID</code> I have meeting this scenario) that will capture the above criteria. If the <code>PO_ID</code> / <code>BUSINESS_UNIT</code> has a <code>CANCEL_STATUS</code> of 'X' on all of it's row(s) then I don't want that <code>PO_ID</code> included in the results.</p>
  <p>I would want the below <code>PO_ID</code> included because even though it does have a row with <code>CANCEL_STATUS = 'X'</code>, it also has other rows with <code>CANCEL_STATUS</code> with other values(other than 'X')</p>
  <p>How can I accomplish identifying <code>PO_ID</code>'s where this occurs?</p>
  <p><a href="https://i.stack.imgur.com/6sne2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6sne2.png" alt="enter image description here" /></a></p>
  <pre><code>SELECT LINE.BUSINESS_UNIT, LINE.PO_ID, LINE.LINE_NBR, LINE.CANCEL_STATUS, LINE.CHANGE_STATUS
  FROM PS_PO_LINE LINE
  INNER JOIN PS_PO_HDR HEADER ON HEADER.BUSINESS_UNIT = LINE.BUSINESS_UNIT 
   AND HEADER.PO_ID = LINE.PO_ID
  --ONLY USING THE FOLLOWING PO_ID FILTER TO ILLUSTRATE THIS EXAMPLE!!
  WHERE LINE.PO_ID = 'A010027905'
   AND HEADER.PO_STATUS NOT IN ('C','X','PX')
  </code></pre>