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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
