I'm getting duplicate rows returned. I don't know know and can't find
out how to construct the SQL to return what I want. I have an old
version of postgres which I cannot upgrade. I'm not even sure how to
get the version. Does this help?
$ grep -i version ..../pi/bin/setup
wtversion='2.0'
export wtversion
My SQL knowledge is quite limited so I've searched all over the net
and read doco but I can't figure this out.
I want to find the total number of hours worked on all projects with
projects.parent="Projects", projects.pct<100, restrictions.hidden=5
(not hidden)
I keep getting duplicate records and I think it's it's becuase I'm
joining a table on 2 other tables. I've tried INTERSECT but I keep
getting parse errors and can't work out why that is. I've tried
creating a temp table but still have to do the twin joins!! I've also
tried creating a view but without success
Here's an example of correct data, a single user and a single project
select username, sum(hours)
from timerecs
where project like 'Testing'
and username = 'long'
group by username
;
username |sum
---------+---
long |127
but there will be many projects to process and to select the
applicable projects requires test on 2 other tables, projects and
restrictions
$the_sql = " SELECT projectname, username, sum(hours)";
$the_sql .= " FROM timerecs";
$the_sql .= " WHERE projectname = projects.projectname ";
$the_sql .= " AND projectname = restrictions.projectname";
$the_sql .= " AND projects.parent = 'Projects'";
$the_sql .= " AND projects.pct < 100";
$the_sql .= " AND restrictions.hidden = 5";
$the_sql .= " AND projectname = 'Testing'"; # just for tsting
$the_sql .= " AND username = 'long'"; # just for testing
$the_sql .= " AND projectname = projects.projectname ";
$the_sql .= " GROUP BY projectname, username";
$the_sql .= " ORDER BY projectname, username";
$the_sql .= " ;";
produces
1 Testing|long|254
How do I get the right list of projectname from timerecs by joining
with the projects and restrictions tables?
I've tried SELECT DISTINCT projectname but make no difference. If I
take the 'restrictions' join out it's fine. I've also tried prefacing
all column names with table names without any change
Thanks
