count with text fields

• swush

  SSC Rookie

  Points: 31

  More actions

  June 7, 2002 at 3:07 am

  #101164

  Hi everyone,i have another question

  currently in my database there are 2 tables which basically look like

  parenttable

  -------------------

  id int

  txtfield text

  childtable

  -------------------

  id int

  now i'd like to select all values from the parenttable and the number of childs in the childtable. "select p.id,p.txt,count(c.id) from parenttable p inner join childtable c on p.id=c.id group by c.id" won't work because i need to group by all values in the select statement, which is not possible because you can't group by a text field.

  any idea?

  jan

• Klaas-Jan

  SSCrazy

  Points: 2297

  More actions

  June 7, 2002 at 3:55 am

  #430488

  select * from (select P.id, count(c.id) as numberofChilds from tblParent P inner join

  tblchild C on p.id=c.id

  group by p.id )T inner join tblParent P2 on T.id=p2.id