Exclude rows with unique values in one column from grouped mysql query

Question

The following table represents interactions form users with different projects:

id         user_id    project_id
17373      6336       1001
17374      6336       1001
17375      6336       1001
529044     6336       1001
17305      6342       1003
17350      6318       1003
17388      6318       1003
811062     6342       1003
811063     6342       1003
1552793    6342       1003

I want to see which users are interacting with each other in the same projects. In other words, I would like to exclude

1. rows where a project_id is associated with only one user (row 1-4), because there are no interactions then.
2. and duplicate rows (last 4 rows), because I only want to know if there is an interaction or not (the intensity does not matter to me).

The query should result in:

id         user_id    project_id
17305      6342       1003
17350      6318       1003

Showing that user 6342 and 6318 are interacting in project 1003.

To achieve that I would start with grouping the results:

SELECT id, user_id, project_id
FROM t
GROUP BY user_id, project_id

Result:

id         author_id  project_id
17268      6336       1001
17350      6318       1003
17305      6342       1003

How can I now exclude the results with a unique project_id (row 1)? Adding HAVING COUNT(*) > 1 does not work.

This is the desired result:

id         author_id  project_id
17350      6318       1003
17305      6342       1003

Answer 1

You can use COUNT(DISTINCT xxx) in this way:

select distinct user_id, project_id
from   t
where  project_id in (select   project_id
                      from     t
                      group by project_id
                      having   count(distinct user_id) > 1);

user_id | project_id
------: | ---------:
   6342 |       1003
   6318 |       1003

db<>fiddle here