How to count number of distinct values when joining multiple tables?

Question

I have following database tables

categories
id, name

products
category_id, id, product_name, user_id

product_comments
product_id, id, comment_text, user_id

I need a count of number of different users in both products and product_comments tables. I have got the following query where I select all those categories where there is atleast one product and each product may have zero or some comments ... but what I can't figure out is that how to get the sum of these different user ids .. if it were just from one table I would try COUNT(products.user_id) ... .here is my query ..

SELECT 
c.*
FROM categories c
INNER JOIN products p ON p.category_id = c.id
LEFT JOIN product_comments pc ON pc.product_id = p.id

I need total number of different users IDs from both products and product_comments tables.

I would expect the result data somewhat like below:

Category_id, Category_name, TotalUsers
1, Test Category, 10
2, Another Category, 5
3, Yet another cat, 3

Answer 1

This will give you an overall count rather than a list of all distinct id's:

SELECT COUNT(DISTINCT user_id)
FROM products
LEFT JOIN product_comments comm ON products.id = comm.product_id

Answer 2

If you want distinct users, then you could try something like:

select distinct user_id from (
   select user_id from products
   UNION
   select user_id from product_comments
) as allusers; 

You can then count them:

select count(distinct user_id) from (
   select user_id from products
   UNION
   select user_id from product_comments
) as allusers;