Received: from liam.avenza.com (liam.avenza.com [205.207.138.1]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA66269 for ; Fri, 29 Oct 1999 16:46:59 -0400 (EDT) (envelope-from andrew@avenza.com) Received: from avenza.com ([192.168.1.20]) by liam.avenza.com (8.9.3/8.9.3) with ESMTP id QAA27672 for ; Fri, 29 Oct 1999 16:53:22 -0400 Message-ID: <381A072E.A1977EFE@avenza.com> Date: Fri, 29 Oct 1999 16:44:30 -0400 From: Andrew Patterson Organization: Avenza X-Mailer: Mozilla 4.7 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Combining Two Tables Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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