Internal Emails - custom query

Question

Hi, This is my first time posting here so hope I'm in the right place. I'm looking to run a Custom Query to get all internal emails (i.e. where all recipients are internal). I've written the following query but it isn't yielding any results - does anyone know what I might be doing wrong? 
 
 select * from emails e 
 inner join emails_text et on e.id=et.email_id 
 inner join emails_email_addr_rel er on e.id=er.email_id 
 inner join email_addresses ea on er.email_address_id = ea.id 
 where e.id not in 
 (select distinct email_id from emails_email_addr_rel emr 
 inner join email_addresses ema on emr.email_address_id=ema.id 
 where email_address not like '%@myorganisation.org%')

Robert Timms · Accepted Answer

Hi Robert Timms 
 
 I believe that this query accomplishes the requirement SELECT e.id email_id, COUNT(eafm.id) from_my, COUNT(eafo.id) from_other, COUNT(eatm.id) to_my, COUNT(eato.id) to_other, COUNT(eacm.id) cc_my, COUNT(eaco.id) cc_other
FROM emails e
INNER JOIN emails_text et ON e.id = et.email_id

INNER JOIN emails_email_addr_rel erf ON e.id = erf.email_id AND erf.deleted = 0 AND erf.address_type = 'from'
LEFT JOIN email_addresses eafm ON erf.email_address_id = eafm.id AND eafm.deleted = 0 AND eafm.email_address LIKE '%@myorganisation.org%'
LEFT JOIN email_addresses eafo ON erf.email_address_id = eafo.id AND eafo.deleted = 0 AND eafo.email_address NOT LIKE '%@myorganisation.org%'

INNER JOIN emails_email_addr_rel ert ON e.id = ert.email_id AND ert.deleted = 0 AND ert.address_type = 'to'
LEFT JOIN email_addresses eatm ON ert.email_address_id = eatm.id AND eatm.deleted = 0 AND eatm.email_address LIKE '%@myorganisation.org%'
LEFT JOIN email_addresses eato ON ert.email_address_id = eato.id AND eato.deleted = 0 AND eato.email_address NOT LIKE '%@myorganisation.org%'

LEFT JOIN emails_email_addr_rel erc ON e.id = erc.email_id AND erc.deleted = 0 AND erc.address_type = 'cc'
LEFT JOIN email_addresses eacm ON erc.email_address_id = eacm.id AND eacm.deleted = 0 AND eacm.email_address LIKE '%@myorganisation.org%'
LEFT JOIN email_addresses eaco ON erc.email_address_id = eaco.id AND eaco.deleted = 0 AND eaco.email_address NOT LIKE '%@myorganisation.org%'

WHERE e.deleted = 0
GROUP BY e.id
HAVING from_other = 0 AND to_other = 0 AND cc_other = 0;