Need help with QUERY to list certain users

[Bellinis]

Hi everyone,

I'm trying to create a Query on my database which will show me all member with more than 2 messages in their inbox.

From these users I also want to know their username, their email AND the amount of messages they have in their inbox.


I would be more than happy if someone could give it a try :rolleyes:

Thanks very much in advance,

Bellinis

[Andreas]

select username, email, pmcount from
(select username, email, count(pmid) as pmcount from pm AS pm left join user AS user on (user.userid=pm.userid) where folderid=0 group by pm.userid) AS subselect where pmcount > 2

Please not that this needs to be adjusted if you are using Table-Prefixes.

[Dean C]

Note the above query only works in MySQL4.1+ :)

[Andreas]

Well ... yes.
Doesn't everybody use that by now ;)