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
