I may have have the wrong end of the stick here, but I thought that an outer join was supposed to give me all the records that were in either the (say) left table, along with the matching results from the right table, and nulls where there was no match.
I have 3 tables I need to query. Person and Detail have a direct 1:1 relationship. The third table is Time. This stores hours worked on a weekly basis, per person, per project. I need a count of the number of weeks each person has put down to.
The following gives me the number of weeks that each user has put time down to as long as they have put down > 1 week
SELECT name, detail.clock, COUNT(DISTINCT(week))
FROM person, detail,
WHERE person.ref = detail.person
AND detail.clock = time.clock
WHERE time.week >= "2010-07-01"
GROUP BY detail.clock
i.e. this will show the 80/100 people who have entered time.
However I need to see those 20 people who have not yet put time down, so I tried the following outer join
SELECT name, detail.clock, COUNT(DISTINCT(week))
FROM person LEFT OUTER JOIN detail ON person.ref = detail.person
LEFT OUTER JOIN time ON detail.clock = time.clock
WHERE time.week >= "2010-07-01"
GROUP BY detail.clock
However this gives me exactly the same result as the first query.
EDIT: I've just discovered part of the problem. In the original version of the pseudo code there was no flittering by date. I've just found that removing the date filter the query behaves more as expected - albeit too slow to be useful, and the date is important.