Okay, I've seen similar posts like this put up before and unfotunately
either they've never been answered or the answer didn't seem to help. So
here goes again :)
Basically I've got two tables. I've got a list of employees, each with a
unique number, and I've got a table of activities that track just what
they're up to. It's conencted via a many-to-one relationship on that
employee number (emp_num).
So what I'm trying to do is get a list of all the employees who've done
a specific event. So the basic SQL would be
SELECT count(*), other_data, etc FROM employee, activity
WHERE activity.emp_num=employee.emp_num AND other_criteria;
The problem with this is that it returns no records for those employees
who haven't done that that type of activity, eg. I'd get this:
emp_num | count | other
-----------------------
0000001 5 foo
0000002 11 foobar
0000004 6 foo2
instead of this, which is what I'd like to see:
emp_num | count | other
-----------------------
0000001 5 foo
0000002 11 foobar
0000003 0 numbar <---- excluded record from first set
0000004 6 foo2
What I used to do was the following hack. I UNIONed the first SQL with
a second one, that looked like this:
SELECT 0, other_data, etc FROM employee, activity
WHERE activity.emp_num=employee.emp_num AND other_criteria;
That worked. But I've just installed the newest version of pgSQL and
that trick now returns two records for every employee, a zero and their
actual count. Strangely enough, even those employees who have zero
counts have two entries. Seeing that, I thought the original SQL with
just a straight count() would work now. It didn't. It still excludes
those employees with zero records (which makes sense).
So my question is twofold:
i) Why am I getting two records for those employees with zero counts
when I use UNION now?
ii) More importantly, how do I get the result I want?
Any help would be *greatly* appreciated.
Wood Shavings! - Andrew