Re: Tricky join question

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