Query to get posts per board?

[imported_rom]

What would be the query to get the results of member posts per board?

ie. with seven boards...

member name id1 id2 id3 id4 id5 id6 id7
member1 12 18 14 16 23 97 1004
member2 13 12 19 26 28 1207 404

etc.

[Eikinskjaldi]

When you say "board" do you mean separate installations of vb each with its own database (I assume all running on the same server) or a board on which you allow multiple logins per member?

[imported_rom]

Originally Posted by Eikinskjaldi

When you say "board" do you mean separate installations of vb each with its own database (I assume all running on the same server) or a board on which you allow multiple logins per member?

I apologize.

One installation of vB, multiple forums (sorry I said boards). No multiple logins per member.

We are a sports community and want to have a poll. But to vote in the poll, members must have over 100 posts on forums where the forum id is in (2,5,10,12,15).

Thanks!

[Eikinskjaldi]

Originally Posted by imported_rom

I apologize.

One installation of vB, multiple forums (sorry I said boards). No multiple logins per member.

We are a sports community and want to have a poll. But to vote in the poll, members must have over 100 posts on forums where the forum id is in (2,5,10,12,15).

Thanks!

Ahh, well in that case...

SELECT count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) and userid=whatever

or

SELECT count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) and username=whatever

[imported_rom]

Originally Posted by Eikinskjaldi

Ahh, well in that case...

SELECT count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) and userid=whatever

or

SELECT count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) and username=whatever

Well we have 4K+ members so I wanted to get a list that tells me which members (by member name) qualify with 100 posts and how many total posts.

THANKS!!!!!!

[Eikinskjaldi]

SELECT userid, username, count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) group by userid order by total desc

[imported_rom]

Originally Posted by Eikinskjaldi

SELECT userid, username, count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) group by userid order by total desc

Thank you!

[Dismounted]

Originally Posted by Eikinskjaldi

SELECT userid, username, count(*) as 'total' FROM post p join thread t USING(threadid) where forumid in (2,5,10,12,15) group by userid order by total desc

Wow, I can hardly decipher that .
[sql]SELECT userid, username, count(*) AS total
FROM post p
JOIN thread t USING(threadid) WHERE forumid IN (2,5,10,12,15)
GROUP BY userid
ORDER BY total DESC[/sql]

[Eikinskjaldi]

Thought of something else that might help

[sql]SELECT userid, username, count(*) AS total
FROM post p
JOIN thread t USING(threadid) WHERE forumid IN (2,5,10,12,15)
GROUP BY userid
HAVING total >= 100
ORDER BY total DESC[/sql]