Re: Tricky join question - Mailing list pgsql-general

Hi Andreas

> First, you should use referential integrity:

I do, that is not the point. It was a simplified data model. Of course I
have primary keys and stuff, but they don't affect join behaviour at all.
>
> 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
>

Well, you shouldn't, thats not my problem.
>
>
>
>>
>> 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)

This is absolutely not what I want. I want a row for every person and
every course, regardless whether the person has taken the course or not.
   If the person has not taken the course, I want a null value in the
person id column:

SELECT c.id, c.name, pc.person_id
FROM  person as p
       left outer join person_course as pc on p.id = pc.person_id
       right outer join course as c on pc.course_id = c.id
where p.id = 2 order by 1;

+---+-----------------------------------------+----------+
| id| name                                    | person_id|
+---+-----------------------------------------+----------+
| 1 | SQL Beginner                            |          |
| 2 | SQL Advanced                            |          |
+---+-----------------------------------------+----------+

Note here that I restrict my select to the person with the ID 2. Since
this person has not taken any course, the person_id is null. If I leave
the restriction on the person, I get person times courses rows, the
person_id only filled when a person has actually taken a course.

With the rows you added person_course and without restrictin to a
specific person, the result of your query should be:


+---+-----------------------------------------+------------+
| id| name                                    | person_id  |
+---+-----------------------------------------+------------+
| 1 | SQL Beginner                            |  1         |
| 1 | SQL Beginner                            |            |
| 1 | SQL Beginner                            |  3         |
| 2 | SQL Advanced                            |            |
| 2 | SQL Advanced                            |            |
| 2 | SQL Advanced                            |  3         |
+---+-----------------------------------------+------------+

In mysql, you get this with the following clause:

SELECT c.id, c.name, pc.person_id
FROM  person as p
       left outer join person_course as pc on p.id = pc.person_id
       right outer join course as c on pc.course_id = c.id
order by 1;

> Please, read more about referential integrity.

Thanks, but I already have read a lot about it 14 years ago.

Bye
Tim