Re: Tricky join question

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