Combining Two Tables

From: Andrew Patterson <andrew(at)avenza(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Combining Two Tables
Date: 1999-10-29 20:44:30
Message-ID: 381A072E.A1977EFE@avenza.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Oleg Bartunov 1999-10-29 20:47:49 Re: [SQL] random query
Previous Message Oleg Bartunov 1999-10-29 20:31:27 random query