title: MySQL UNION where clause condition from the first select
body: <p>How can union two selections of the same tables but the second select condition depends on the first select attribute. Here is my query</p>
  
  <pre><code>SELECT *
  FROM tbl_preference_header h
  LEFT JOIN tbl_preference_detail d
  OJ h.id = d.superid
  WHERE h.type = 'CP' 
  UNION
  SELECT *
  FROM tbl_preference_header h2
  LEFT JOIN tbl_preference_detail d2
  ON h2.id = d2.superid
  WHERE h2.type = 'GP' AND d2.cat3code NOT IN (d.cat3code)
  </code></pre>
  
  <p>What I want is in the second select statement it will not contain all the cat3code from first select statement. There is error in my query d is not recognized in the second select statement.</p>
  
  <p>How can I accomplish this ? What another method can I use other than union ?</p>