How to Join three tables properly

The main table has 4 columns:

User Activity Table

userActivityId  userId  therapistId activityId
1                1           1        1

Each of these columns is a table and these values are all foreign keys.

Basically im trying to run a query that will join to the users table and pull their first and last name based off the user Id.Same thing with therapist – join to the therapist table, pull first + last name.And finally Join to the Activity table and pull the activity name and path from the activity Id

The other tables look like this:

User Table
userId  fName   lName


Therapist Table
therapistId therapistFirstName  therapistLastName


Activity Table
activityId  activityTitle   activityPath

So far my query looks like

SELECT 
User_Activities.userId, 
User_Activities.therapistId, 
User_Activities.activityId, 
Activities.activityTitle, 
Activities.activityPath, 
Users.fName, 
users.lName, 
Therapists.therapistFirstName, 
Therapists.therapistLastName 
FROM 
User_Activities 
INNER JOIN Users 
ON User_Activities.userId = Users.userId
INNER JOIN Therapists ON 
User_Activities.therapistId = Therapists.therapistId
INNER JOIN Activities ON
Activities.activityId = User_Activities.userActivityId
WHERE 
User_Activities.userId = 1;

When I run this query It only returns 1 row as a result. However there are two activities in the User_Activites table assigned to userId 1.

If I change : INNER JOIN Activities ON Activities.activityId = User_Activities.userActivityId

from an INNER JOIN to the LEFT JOIN it will display the second row, however the activityTitle and activityPath will be displayed as NULL in the second row.

userActivityId  userId  therapistId     activityId    activityId    activityTitle   activityPath    fName   lName   therapistFirstName  therapistLastName   
1                 1           1              1            1         Brain           GZZ0zpUQ         S        C            M                      D
11                1           1              1           NULL              NULL            NULL      S        C             M                        D

Answer

You have pretty much answered your question. The second activity does not have a valid ActivityId.

If you want all activities for a user, then you should phrase the query as:

SELECT . . .
FROM Users u LEFT JOIN
     User_Activities ua
     ON ua.userId = u.userId LEFT JOIN
     Therapists t
     ON ua.therapistId = t.therapistId LEFT JOIN
     Activities a
     ON a.activityId = ua.userActivityId
WHERE u.userId = 1;

You want to start with the table where you want to keep all the rows. Then use LEFT JOIN to bring in other tables.

Two other changes of note:

Table aliases are used to simplify reading and writing the query. The SELECT needs to change to use the aliases.
The WHERE clause refers to the Users table rather than UserActivities.

Advertisement

Answer