SELECT 
  pt.hash as hash, 
  count(pm.id) AS unread 
FROM 
  cscart_privat_messages AS pm 
  LEFT JOIN cscart_privat_threads AS pt ON pt.id = pm.thread_id 
WHERE 
  pm.`read` = 'N' 
  AND pm.user_id != 0 
  AND pm.status IN('S', 'A') 
  AND pm.visible_to IN (0, 0) 
  AND pm.thread_id IN (
    SELECT 
      thread_id 
    FROM 
      cscart_privat_users 
    WHERE 
      user_id = 0
  ) 
GROUP BY 
  pt.hash