From: Andrew Patterson <andrew@avenza.com>
To: pgsql-sql@postgresql.org
Subject: Combining Two Tables
Date: Fri, 29 Oct 1999 16:44:30 -0400
Message-ID: <381A072E.A1977EFE@avenza.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: andrew@avenza.com
Subject: Re: Combining Two Tables
In-Reply-To: <381A072E.A1977EFE@avenza.com>
References: <381A072E.A1977EFE@avenza.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox