Re: [sqlite] DISTINCT on a JOIN

2011-09-23 Thread Frank Missel 
> 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

This can be produced by:

select crs.id
 , crs.name
 , (select count(id) from people pe where pe.course = crs.id) as
num_people
 , (select count(id) from tasks ta where ta.course = crs.id)as
num_tasks
from courses crs
;

Result:
14  starter  course  2   2



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DISTINCT on a JOIN

2011-09-23 Thread Simon Slavin 

On 23 Sep 2011, at 12:57pm, Igor Tandetnik wrote:

> select name,
>(select count(*) from people where course=courses.id),
>(select count(*) from tasks where course=courses.id)
> from courses;

Thank you thank you thank you Igor.

I must learn how to use sub-selects properly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DISTINCT on a JOIN

2011-09-23 Thread Igor Tandetnik 
Simon Slavin  wrote:
> 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

select name,
(select count(*) from people where course=courses.id),
(select count(*) from tasks where course=courses.id)
from courses;

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DISTINCT on a JOIN

2011-09-23 Thread Simon Slavin 
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