php - MySQL query for finding relation between three tables with constraints, even if the relation doesn't exist -

i working on online university has users, courses, , user course status. have list of users, , list of courses. want find course status of given users , courses, including null status course user hasn't yet started.

so example:

user ids: [1, 7, 14, 21]   course ids: [5, 8, 36, 50] 

desired result:

name        course                     status  john doe    how tie shoes      complete  john doe    how paint house    in progress  jane doe    how tie shoes      complete  jane doe    how paint house    not started <-- these tricky ones 

...

it seems can perform left join on tables , null values, can coalesce 'not started' add constraints limit courses and/or users i'm looking for...it stops giving me null values values because null course id isn't in list of courses above.

here's example query giving idea of i've been trying (among other things):

select     `users`.`name` `name`,     `users`.`email` `email`,     `courses`.`number` `course #`,     `courses`.`name` `course`,     coalesce(`courses_users_statuses`.`name`, 'not started') `status`     `users`     left join `courses_users`          on `courses_users`.`user_id` = `users`.`id`     left join `courses`          on `courses`.`id` = `courses_users`.`course_id`     left join `courses_users_statuses`          on `courses_users_statuses`.`id` = `courses_users`.`status_id`     `courses`.`id` in ([1, 2, 3, 4, 5, 10, 11, 12, 16, ...])     , `users`.`id` in ([1, 2, 3, 4, 5, 20, 21, 36, 48, ...]) order     `users`.`name`,     `courses`.`number` 

any ideas on how write this? also, let me know if can provide more detail or more code/table examples.

edit: here's updated query using advice answers below:

select     `users`.`name` `name`,     `users`.`email` `email`,     `courses`.`number` `course #`,     `courses`.`name` `course`,     coalesce(`courses_users_statuses`.`name`, 'not started') `status`     `users`         left join     `courses_users` on `courses_users`.`user_id` = `users`.`id`         left join     `courses` on `courses`.`id` = `courses_users`.`course_id` , `courses`.`id` in (1, 2, 3, 4, 5)         left join     `courses_users_statuses` on `courses_users_statuses`.`id` = `courses_users`.`status_id`     `users`.`id` in (1, 2, 3, 4, 5) order     `partners`.`name`,     `users`.`name`,     `courses`.`number` 

this updated example improvement, it's showing records there no course name or number, there status. i'm not sure how it's grabbing status course relations should exist. instead should null (or "not started"). here's sample data database:

`users` table:

id    name             email 1     stevie mccomb    test@example.com 2     john doe         test@example.org 3     jane doe         test@example.net 

`courses` table:

id    number    name 1     101       navigation 2     102       logging in 3     103       updating records 4     104       managing users 

`courses_users` table:

course_id    user_id    status_id    completed_at 1            1          2            2017-01-01 00:00:00 3            1          1            2017-01-05 00:23:00 1            2          2            2017-04-13 15:00:37 

`courses_users_statuses` table:

id    name           slug 1     in progress    progress 2     complete       complete 

desired result:

name             email               course #    course              status stevie mccomb    test@example.com    101         navigation          complete stevie mccomb    test@example.com    102         logging in          not started stevie mccomb    test@example.com    103         updating records    in progress stevie mccomb    test@example.com    104         managing users      not started john doe         test@example.org    101         navigation          complete john doe         test@example.org    102         logging in          not started john doe         test@example.org    103         updating records    not started john doe         test@example.org    104         managing users      not started jane doe         test@example.net    101         navigation          not started jane doe         test@example.net    102         logging in          not started jane doe         test@example.net    103         updating records    not started jane doe         test@example.net    104         managing users      not started 

current result:

name             email               course #    course              status stevie mccomb    test@example.com                                    complete stevie mccomb    test@example.com                                    not started stevie mccomb    test@example.com    103         updating records    in progress stevie mccomb    test@example.com                                    not started john doe         test@example.org    101         navigation          complete john doe         test@example.org                                    not started john doe         test@example.org                                    not started john doe         test@example.org                                    not started jane doe         test@example.net                                    not started jane doe         test@example.net                                    not started jane doe         test@example.net                                    not started jane doe         test@example.net                                    not started 

the problem you're putting constraints in clause, without allowing null value.. changes left join inner join.

to fix this, can either explicitly allow nulls, or can move logic join clause (my preference).

select     `users`.`name` `name`,     `users`.`email` `email`,     `courses`.`number` `course #`,     `courses`.`name` `course`,     coalesce(`courses_users_statuses`.`name`, 'not started') `status`     `users`     left join `courses_users`          on `courses_users`.`user_id` = `users`.`id`     left join `courses`          on `courses`.`id`   = `courses_users`.`course_id`         , `courses`.`id` in ([1, 2, 3, 4, 5, 10, 11, 12, 16, ...])     left join `courses_users_statuses`          on `courses_users_statuses`.`id` = `courses_users`.`status_id`     `users`.`id` in ([1, 2, 3, 4, 5, 20, 21, 36, 48, ...]) order     `users`.`name`,     `courses`.`number`