Combining Two Tables
От | Andrew Patterson |
---|---|
Тема | Combining Two Tables |
Дата | |
Msg-id | 381A072E.A1977EFE@avenza.com обсуждение исходный текст |
Список | 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
В списке pgsql-sql по дате отправления: