Select where linked tables have a certain count?

[Strahan]

Hi.  I have a table "info" with fields "id" and "name" then I have a table "files" with fields "fileid", "parent", "filename".  My data for example would be:

 

INFO:

 

id, name

1, Documents

2, Media

 

MEDIA:

 

fileid, parent, filename

1, 1, expenses.xls

2, 1, accounts.xls

3, 1, letter.doc

4, 2, music.mp3

 

I want to be able to return a list of names in info where there are less than 3 files in "files".  I tried SELECT name FROM info i INNER JOIN files f ON f.parent = i.id WHERE COUNT(f.fileid) < 3 but it said "Invalid use of group function".

 

What am I doing wrong?  How could I achieve this?  Thanks!

[Barand]

try

SELECT name
FROM info i
INNER JOIN files f ON f.parent = i.id
GROUP BY name
HAVING COUNT(f.fileid) < 3