I have a setup which I will simplify as follows: There is a table of courses. Every course can have any number of people working on it. Every course involves any number of tasks.
I want to make a SELECT which will return a table as follows: course1 number-of-people-involved-in-course1 number-of-tasks-involved-in-course1 course2 number-of-people-involved-in-course2 number-of-tasks-involved-in-course2 course3 number-of-people-involved-in-course3 number-of-tasks-involved-in-course3 course4 number-of-people-involved-in-course4 number-of-tasks-involved-in-course4 ... I can do one or the other but not both. Here's some text commands for you to play around with. CREATE TABLE courses (id INTEGER PRIMARY KEY,name TEXT); CREATE TABLE people (id INTEGER PRIMARY KEY,course INTEGER,name TEXT); CREATE TABLE tasks (id INTEGER PRIMARY KEY,course INTEGER,name TEXT); INSERT INTO courses VALUES (14,'starter course'); INSERT INTO people VALUES (21,14,'erica'); INSERT INTO people VALUES (22,14,'rod'); INSERT INTO tasks VALUES (31,14,'teaching'); INSERT INTO tasks VALUES (32,14,'marking'); sqlite> SELECT courses.name,group_concat(people.name) FROM courses LEFT JOIN people ON people.course=courses.id GROUP BY courses.id; starter course|erica,rod sqlite> SELECT courses.name,group_concat(tasks.name) FROM courses LEFT JOIN tasks ON tasks.course=courses.id GROUP BY courses.id; starter course|marking,teaching BUT ... sqlite> SELECT courses.name,group_concat(people.name),group_concat(tasks.name) FROM courses LEFT JOIN people ON people.course=courses.id LEFT JOIN tasks ON tasks.course=courses.id GROUP BY courses.id; starter course|erica,erica,rod,rod|marking,teaching,marking,teaching sqlite> SELECT courses.name,group_concat(people.name),group_concat(tasks.name) FROM courses LEFT JOIN people ON people.course=courses.id LEFT JOIN tasks ON tasks.course=courses.id GROUP BY courses.id,people.id,tasks.id; starter course|erica|teaching starter course|erica|marking starter course|rod|teaching starter course|rod|marking probably because ... sqlite> SELECT * FROM courses LEFT JOIN people ON people.course=courses.id LEFT JOIN tasks ON tasks.course=courses.id; 14|starter course|21|14|erica|32|14|marking 14|starter course|21|14|erica|31|14|teaching 14|starter course|22|14|rod|32|14|marking 14|starter course|22|14|rod|31|14|teaching sqlite> SELECT * FROM courses LEFT JOIN people ON people.course=courses.id LEFT JOIN tasks ON tasks.course=courses.id GROUP BY courses.id; 14|starter course|22|14|rod|31|14|teaching Is there some way of using DISTINCT or some other tactic which will make it list each related entry just the once ? Very grateful for any help. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
