Help with subselect

[tiborv]

Hello..

can anyone look at this mess pls!
I need to get counted user_members from es_h_user_members with key h_user_id.

CODE
SELECT r.h_user_id, r.h_user_name, r.h_user_lastname, b.help_user_id, MAX(b.help_user_date) AS last_user_help, c.h_um_user_id
FROM es_h_users r, es_h_users_help b, es_h_user_members c
WHERE r.h_user_id = b.help_user_id
AND (SELECT COUNT(h_um_user_id) FROM es_h_user_members WHERE h_um_user_id = r.h_user_id)
GROUP BY r.h_user_id
ORDER BY MAX(b.help_user_date) ASC
LIMIT 100

I would really like to have somethink like
SELECT r.h_user_id, r.h_user_name, r.h_user_lastname, b.help_user_id, MAX(b.help_user_date) AS last_user_help, COUNT(c.h_um_user_id)
FROM es_h_users r, es_h_users_help b, es_h_user_members c
WHERE r.h_user_id = b.help_user_id
AND r.h_user_id = c.h_um_user_id
GROUP BY r.h_user_id
ORDER BY MAX(b.help_user_date) ASC
LIMIT 100

THX!

[tomhath]

Something like this should run, but I'm not sure what you expect in the "COUNT(c.h_um_user_id)" field; can there be multiple rows in es_h_user_members for a h_um_user_id?

SELECT r.h_user_id, r.h_user_name, r.h_user_lastname, 
	MAX(b.help_user_date) AS last_user_help, COUNT(c.h_um_user_id)
FROM es_h_users r
INNER JOIN es_h_users_help b
  ON r.h_user_id = b.help_user_id
INNER JOIN es_h_user_members c
  ON r.h_user_id = c.h_um_user_id
GROUP BY r.h_user_id, r.h_user_name, r.h_user_lastname
ORDER BY MAX(b.help_user_date) ASC
LIMIT 100