0

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!

Jason van der Zeeuw
  • 923
  • 1
  • 11
  • 29

1 Answers1

0

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"
Phil Cross
  • 9,017
  • 12
  • 50
  • 84
  • I actually want to return something like this: $user1->array-with-tasks, $user2->array-with-tasks, etc. The array with tasks could then be NULL, but I'll still have access to the other userfields. With this query I still see only 2 out of 8 users, with one record per task. – Jason van der Zeeuw May 17 '13 at 12:33
  • How many records in `tasks` have the date field as 2013-05-16 ? 2 or 8? – Phil Cross May 17 '13 at 12:38
  • 3 fields contain that date, 2 of them belong to one user, and 1 of them belongs to another user, look here: http://pastebin.com/7TsZS7dk – Jason van der Zeeuw May 17 '13 at 12:45