Luciano Nascimento Luciano Nascimento - 10 days ago 7
MySQL Question

Mysql count(*) based in two relations

I would like to count(*) how much customers have created a post or made a comment. If the same customer has made several posts and comments, it should count only once.




Customer Table:

ID
Name
...


1
Jonh


2
Mark


3
King


4
Doe



Post Table:

ID
USER_ID
...


1
1


2
1


3
3


4
1



Comment Table:

ID
USER_ID
...


1
1


2
3


3
3


4
4



It should return count(*) = 3

(user_id: 1, 3 and 4).

Answer

Try this one. It worked for me and returns what you're looking for:

SELECT COUNT( USER_ID ) AS TOTAL
FROM (
SELECT USER_ID
FROM POSTS
UNION
SELECT USER_ID
FROM COMMENTS
)X

I used POSTS and COMMENTS as table names bc I was unsure what your exact table names are, so make sure to change these in your query.