SELECT sql_calc_found_rows *, count(*), Domain.id, Domain.href, count(distinct Url.id) as url_count, count(distinct UrlContact.contact_id) as contact_count, User.id, User.first_name, User.last_name, MIN(AgentUrl.id) AS DiscoveryOrder
FROM domains Domain
LEFT OUTER JOIN project_domains ProjectDomain on Domain.id=ProjectDomain.domain_id
LEFT OUTER JOIN users User on User.id=ProjectDomain.assigned_to_user_id, contacts Contact, url_contacts UrlContact, urls Url, agent_urls AgentUrl
WHERE Url.domain_id=Domain.id
AND UrlContact.contact_id=Contact.id
AND Contact.dead=0
AND UrlContact.url_id=Url.id
AND AgentUrl.agent_id=377
AND AgentUrl.url_id=Url.id
AND NOT EXISTS
(SELECT * FROM project_domains ProjectDomain WHERE ProjectDomain.domain_id=Domain.id AND ProjectDomain.hidden=1 AND ProjectDomain.project_id=11)
AND 377=(select MIN(AgentUrl2.agent_id)
FROM agent_urls AgentUrl2, urls Url2, agents Agent2
WHERE AgentUrl2.url_id=Url2.id
AND Agent2.id=AgentUrl2.agent_id
AND Agent2.project_id=11
AND Url2.domain_id=Domain.id)
GROUP BY Domain.id
HAVING contact_count>0
ORDER BY DiscoveryOrder ASC, contact_count desc
LIMIT 0,100