SELECT h.hostid, h.host
FROM hosts h
JOIN hosts_groups hg1 ON h.hostid = hg1.hostid
JOIN hosts_groups hg2 ON h.hostid = hg2.hostid
WHERE hg1.groupid = 1
  AND hg2.groupid = 2;