am Fri, dem 22.12.2006, um 12:12:06 +0100 mailte Tim Tassonis folgendes: > Hi all > > I have a join problem that seems to be too difficult for me to solve: > > I have: > > table person > id integer, > name varchar(32) > > data: > > 1,"Jack" > 2,"Jill" > 3,"Bob" > > > table course > id integer, > name varchar(32) > > data: > > 1,"SQL Beginner" > 2,"SQL Advanced" > > > > table person_course > person_id number, > course_id number > > data:
First, you should use referential integrity: test=# create table person(id int primary key, name text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person" CREATE TABLE test=# create table course(id int primary key, name text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "course_pkey" for table "course" CREATE TABLE test=# create table person_course(person_id int references person, course_id int references course ); CREATE TABLE > > (currently empty) Okay, i insert some data: test=# insert into person_course values (1,1); INSERT 0 1 test=# insert into person_course values (3,1); INSERT 0 1 test=# insert into person_course values (3,2); INSERT 0 1 > > > Now, I would like to know for every person the courses they have taken. Similar to your result: test=# select b.id, b.name, c.id from person_course a left join course b on a.course_id=b.id left join person c on a.person_id=c.id; id | name | id ----+--------------+---- 1 | SQL Beginner | 1 1 | SQL Beginner | 3 2 | SQL Advanced | 3 (3 rows) In my opinion better: test=# select c.id, c.name, b.name from person_course a left join course b on a.course_id=b.id left join person c on a.person_id=c.id; id | name | name ----+------+-------------- 1 | Jack | SQL Beginner 3 | Bob | SQL Beginner 3 | Bob | SQL Advanced (3 rows) Please, read more about referential integrity. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
