Users with most comments from Drupal + phpBB

Question

i need to get a list of users with mosts comments + posts from Drupal + phpBB.

I am using mySQL.

Structure of Drupal database: dr_comments(cid,uid) dr_users(uid,name)

phpBB: phpbb_posts(post_id,poster_id) phpbb_users(user_id,username)

I have SQL code for users with most comments from Drupal and phpBB not together.

Drupal:

SELECT
 U.name,
 COUNT(C.cid) AS CommentCount
FROM
 dr_users AS U
 INNER JOIN dr_comments AS C ON U.uid = C.uid
GROUP BY
 U.name
ORDER BY
 COUNT(C.cid) DESC

LIMIT 10

phpBB:

SELECT
 U.username,
 COUNT(C.post_id) AS CommentCount
FROM
 phpbb_users AS U
 INNER JOIN phpbb_posts AS C ON U.user_id = C.poster_id
GROUP BY
 U.username
ORDER BY
 COUNT(C.post_id) DESC

LIMIT 10

I dont know how to merge it together.

Answer 1

You can use UNION:

SELECT name, SUM(CommentCount) AS TotalCommentCount
FROM (
  SELECT U.name, COUNT(C.cid) AS CommentCount
  FROM dr_users AS U
  INNER JOIN dr_comments AS C ON U.uid = C.uid
  GROUP BY U.name

  UNION ALL

  SELECT U2.username AS name, COUNT(C2.post_id) AS CommentCount
  FROM phpbb_users AS U2
  INNER JOIN phpbb_posts AS C2 ON U2.user_id = C2.poster_id
  GROUP BY U2.username

) temp GROUP BY name

That should do its job. Note for the script above I expect that the usernames are on both programmes equal.

You can also check this SQL Fiddle.