Announcement

Collapse
No announcement yet.

Need help with a Query on my user's PM's

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Need help with a Query on my user's PM's

    Hello!

    I was wondering if someone could help me getting a query on my database working. What I would like is the following:

    I'm trying to create a Query that will show me all users 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 tried the query below, but it's not working as expected.

    Code:
    select username, email, pmcount from
    (select username, email, count(pmid) as pmcount from vb_pm AS vb_pm left join user AS user on (vb_user.userid=vb_pm.userid) where folderid=0 group by vb_pm.userid) AS subselect where pmcount > 2
    I would really appreciete it if someone could help me out here.

    Thank you very much in advance,

    Pasqual

  • #2
    I have never messed with subqueries, but this single query seems to work, except that it doesn't eliminate pmcounts <= 2:

    Code:
    SELECT user.username, user.email, COUNT(pm.pmid) AS pmcount
    FROM pm AS pm
    LEFT JOIN user AS user ON (user.userid = pm.userid)
    WHERE pm.folderid = 0
    GROUP BY pm.userid
    ORDER BY pmcount
    DESC

    Comment

    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
    Working...
    X