The Query takes too long to respond ~ 40 minutes.
`SELECT c.consumer_id FROM consumer c
WHERE c.active_flag = 'Y'
AND ( c.frequency = 'Q' )
AND c.consumer_id NOT IN(
FROM (SELECT s.consumer_id
FROM transactions s
WHERE ( s.cycle='2016-Q-2' ) and s.active_flag = 'Y' AND s.status <> 'Door Locked')
Inner Join did the trick for me
SELECT DISTINCT c.consumer_id FROM consumer c INNER JOIN (SELECT DISTINCT consumer_id as sid from transactions where consumer_id not in ( select consumer_id from transactions where cycle = '2016-Q-2' AND active_flag = 'Y' AND status != 'Door Locked' )) as s ON s.sid = c.consumer_id WHERE c.active_flag = 'Y' AND ( c.frequency = 'Q' )
not sure if this the correct way but response time is down to ~ 700ms now.
not sure why but the response of queries from all the above answers gave all the consumer ids available in the transaction table.