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