Reputation: 43
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
Upvotes: 0
Views: 46
Reputation: 1269763
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:
SELECT
needs to change to use the aliases.WHERE
clause refers to the Users
table rather than UserActivities
.Upvotes: 0