具有NULL值的Mysql多连接表

Question

I'm trying to do the following...

I have a PHPmyadmin database wit the tables: tasks, users and a table task_user. For example I want to have all tasks from 2013-01-01, and a task can have multiple users. I want to make a table in HTML which looks like this:

                      Name1       Name2       Name3
Date: 01-01-2013      Task                    Task
                      Task

So the query should always return the users wether they have tasks or not.

If I do something like this:

SELECT t.title, u.firstname, u.lastname FROM users as u
INNER JOIN task_user as tu ON tu.uid = u.uid
INNER JOIN tasks as t ON t.taskid = tu.taskid
WHERE t.date = "2013-05-16"

This only returns ONE user, this user has TWO tasks on this date, but it also only displays ONE task.

How can I get this query to give me all users with all of their tasks? (if they have tasks, else return null) Or does anybody know a better way to achieve this?

I think I might be needing some other JOIN or a GroupBy?

All help will be appreciated!

Answer 1

Use LEFT or RIGHT joins instead of INNER joins. Inner joins will only return records if a record is found in both / all tables

SELECT t.title, u.firstname, u.lastname FROM users as u
LEFt JOIN task_user as tu ON tu.uid = u.uid
LEFT JOIN tasks as t ON t.taskid = tu.taskid
WHERE t.date = "2013-05-16"