Return Parents With Only One Child In a Parent and Child relationship, I'm trying to return Parents that only have one Child and a where clause applied to the only child firstname like 'tom'

t-sql pseudo: parents with only one child and that child's name is tom

select p.* 

from parents p 
  inner join children c on c.parentId = p.id

where count(c.id) = 1 -- ??? with only 1 child ???
  and c.firstname like 'tom'


Any ideas?
Assuming the primary key of children is (horror) id:

select p.* 

from parents p 
  inner join children c on c.parentId = p.id

where c.firstname like 'tom'
  and not exists
      ( select 1 from children c2
        where c2.parentId = p.id
          and c2.id &lt;&gt; c.id
      ) ;


or using GROUP BY:

select p.* 

from parents p 
  inner join 
    ( select parentid
      from children c 
      group by parentid
      having min(id) = max(id)          -- you could use instead:  count(*)=1  here
         and min(firstname) like 'tom'
    ) c
    on c.parentid = p.id ;