How can i get record with must match minimum given ids with SQL IN?

Question

I have tables:

users:

id | name
1  | first name
2  | second name
3  | third name
4  | fourth name

projects:

id | name
1  | first
2  | second

projectInvitees:

id | userId | projectId
1  | 1      |     1
2  | 2      |     1
3  | 2      |     2
4  | 3      |     1

I want a record like: the project which must has minimum both assignee userId with 1 and userId with 2

I tried:

select projectId from projectInvitees where userId IN (1, 2);

but it returns projectId 1 and 2, because I user IN but as I mention I want the only projectId 1

Can any one guide me with this

Answer 1

This variant may help you. For change need users count just change parameter in HAVING statement.

    SELECT projectid
    FROM projectinvitees
    WHERE userId IN (1, 2)
    GROUP BY projectid 
    HAVING count (*) >= 2

Answer 2

you can use a query like this:

SELECT distinct projectId 
FROM projectInvitees
WHERE userId IN (1, 2)
GROUP BY projectId
HAVING count(*)  = 2;

sample

MariaDB [Kazoku]> SELECT * from projectInvitees;
+----+--------+-----------+
| id | userId | projectId |
+----+--------+-----------+
|  1 |      1 |         1 |
|  2 |      2 |         1 |
|  3 |      2 |         2 |
|  4 |      3 |         1 |
+----+--------+-----------+
4 rows in set (0.02 sec)

MariaDB [Kazoku]> select distinct projectId 
    -> from projectInvitees
    -> WHERE userId IN (1, 2)
    -> GROUP BY projectId
    -> HAVING count(*)  = 2;
+-----------+
| projectId |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

MariaDB [Kazoku]> 

Answer 3

Is this what you need?

SELECT
    min(projectid) projectid
FROM
    projectinvitees
WHERE
    userid IN (
        1,
        2
    );