Select with join testing two different datasets on the same column

Question

I have a database on which users has and belong to many "activities", and those activities are grouped by an activity_type:

Let's say i have a Users table

users
-------
id
name
.....

And an Activity table and type

activities
----------
id
name
activity_type_id

.

activity_types
--------------
id
name

And a join table

activities_users
------------------
user_id
activity_id

So the question is:

If i have activity_type ids 5 and 7, and need to make a query that brings the users who have been in activities of type 5 AND 7.

How can then this query be achieved?

Tried this:

SELECT users.id, users.name, users.email, users.phone FROM users
JOIN activities_users ON (
  users.id = activities_users.user_id  AND
  (
    activities_users.activity_id IN (SELECT activities.id FROM activities where activities.activity_type_id IN (5))
    AND
    activities_users.activity_id IN (SELECT activities.id FROM activities where activities.activity_type_id IN (7))
  )
);

But realize i will have an empty set because activity_id on the 5th line will never be in one dataset and the other simultaneously.

Greetings!

score 0 · Answer 1 · answered Nov 10 '17 at 00:15

Well i think i had to make two different JOIN aliasing the join table with a different name each time:

SELECT users.id, users.name, users.email, users.phone FROM users
JOIN activities_users AS au1 ON (
  users.id = au1.user_id  
  AND au1.activity_id IN (SELECT activities.id FROM activities where activities.activity_type_id IN (5))
)
JOIN activities_users AS au2 ON (
  users.id = au2.user_id  
  AND au2.activity_id IN (SELECT activities.id FROM activities where activities.activity_type_id IN (7))
)
group by users.id;

More optimal answers are still appreciated anyways!

score 0 · Answer 2 · answered Nov 10 '17 at 00:17

SELECT *
  FROM users
 WHERE EXISTS
           (SELECT NULL
              FROM activity_types
                   INNER JOIN activities ON activities.activity_type_id = activity_types.id
                   INNER JOIN activities_users ON activities_users.activity_id = activities.id
             WHERE activity_types.id IN (5, 7)
               AND activity_users.user_id = users.id)

Select with join testing two different datasets on the same column

2 Answers2