MySQL UNION where clause condition from the first select

Question

How can union two selections of the same tables but the second select condition depends on the first select attribute. Here is my query

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)

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.

How can I accomplish this ? What another method can I use other than union ?

Answer 1

You won't be able to reference the original query directly, but you could bring the original query into a subquery as follows:

SELECT * FROM tbl_preference_header h left join tbl_preference_detail d on h.id = d.superid where type = 'CP' 

union 

select * 
from 
    tbl_preference_header h2 
        left join tbl_preference_detail d2 on h2.id = d2.superid 
where type = 'GP' and d2.cat3code not in (
    select d.cat3code 
    from 
        tbl_preference_header h 
            left join tbl_preference_detail d on h.id = d.superid 
    where type = 'CP' 
)

Answer 2

It is a bit hard to figure out exactly what you want. If I assume that type is part of tbl_preference_detail, then your query is equivalent to:

SELECT *
FROM tbl_preference_header h left join
     tbl_preference_detail d
     on h.id = d.superid
WHERE d.type = 'CP'  or
      (d.type = 'GP' and
       not exists (select 1
                   from tbl_preference_detail d2
                   where d2.cat3code = d.cat3code and
                         d2.type = 'CP'
                  )
      )