MySQL: joining three tables and returning all rows-CodePudding

I have these three tables:

user (id, name)
activity (id, name)
enrolment (id, user_id, activity_id)

I need to return a list of all users, and for each of them return all the activities as well as whether or not there is an enrolment record for that user and that activity.

So, if I have 3 activities, it would return something like this:

user1     activity1     "not enrolled"
user1     activity2     "enrolled"
user1     activity3     "not enrolled"

user2     activity1     "enrolled"
user2     activity2     "enrolled"
user2     activity3     "not enrolled"
etc...

I've tried a few join combinations but with no luck so far... Any tip would be greatly appreciated.

EDIT:

The solutions for this case, provided by Barmar below, is:

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id AND a.id = e.activity_id
ORDER BY u.name, a.name

However there is an added complication. I tried to simplify the matter by putting an activity_id field in the enrolment record, but actually the link between enrolment and activity is in a separate table:

user (id, name)
activity (id, name)
enrolment (id, user_id)
enrolment_activity (id, activity_id, enrolment_id)

So I've tried the following:

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id
LEFT JOIN enrolment_activity AS ea ON (ea.activity_id = a.id  AND ea.enrolment_id = e.id)
ORDER BY u.name, a.name

But it doesn't seem to return the correct result (lots of duplicates and the enrolment column is not correct anymore)

EDIT 2:

Figured it out by using a subquery. Not sure if that's the most efficient way, but it worked:

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id AND a.id = (SELECT activity_id FROM enrolment_activity WHERE enrolment_id = e.id)
ORDER BY u.name, a.name

CodePudding user response：

Cross JOIN between user and activity to get all possible combinations. Then use LEFT JOIN to determine if the user is enrolled in that activity.

SELECT u.name AS user, a.name AS activity, IF(e.id IS NULL, 'not enrolled', 'enrolled') AS enrolled
FROM user AS u
CROSS JOIN activity AS a
LEFT JOIN enrolment AS e ON u.id = e.user_id AND a.id = e.activity_id
ORDER BY u.name, a.name