Invalid use of group function

You don't group by an aggregate, try removing CountOfName from your GROUP BY list.

true. I missed it

agregate = alias

I had to look twice myself.

except that the Query Optimizer will first strip them off :D

must re-read up on that ...
;-)

as lowfatspread explained you HAVING clause only applies to aggregate functions. but if you use a condition (WHERE) on the left table in a RIGHT JOIN it will transform it in an INNER JOIN, so it should not provide the desired result.
there is something unclear in your query : do you have many names for on photo, or many photos for one name ?
Anyway, could you post few lines of your tables and explain describe the desired result in simple words (not SQL) ?

Hello,

The query i presented has had the desired effect when using MS Access. However I have now moved to MySQL.

Basically there is one photo record with many name records.

mysql> describe names;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| photo | varchar(50) |      | MUL |         |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe photos;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| photo              | varchar(50) |      | PRI |         |       |
| top_level_catagory | varchar(50) | YES  |     | NULL    |       |
| location           | varchar(50) | YES  |     | NULL    |       |
| censored           | tinyint(1)  | YES  |     | NULL    |       |
| catagory           | varchar(50) | YES  |     | NULL    |       |
| user               | varchar(50) |      | MUL |         |       |
| recycled           | tinyint(1)  | YES  |     | NULL    |       |
| date               | date        | YES  |     | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

A photo would have a collection of people associated with it. I want to get a list of people with a number representing how many photos they are in.

Thanks for the help,

Paul.

Did removing countofname from your group by clause work?

Hello,

Thanks for all the help. I just copied and pasted lowFatSpread's sql into my code and it worked.

Squeebee: I tried removing countOfName from my origional sql, it madfe no difference.

I'm not sure why the sql works in MS Access but not in MySQL. I'd be interested to know why? (no points for that tho!)

Anyway, as LowFatSpread gave the correct solution, I have awarded hime the 500 points.

Thanks everyone for the help.

Paul.