Re: Tricky join question

Tim Tassonis <timtas(at)cubic(dot)ch> schrieb:
> 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:

test=# select c.id, c.name, pc.person_id
from course c
cross join person p
left outer join person_course pc on (p.id,c.id)=(pc.person_id,pc.course_id);
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
(6 rows)

But i think, this is a little bit stupid, because row 4 and 5 are the
same.

Perhaps this would be better:

test=# select c.id, c.name, p.id as person, pc.person_id
from course c
cross join person p
left outer join person_course pc on (p.id,c.id)=(pc.person_id,pc.course_id);
id | name | person | person_id
----+--------------+--------+-----------
1 | SQL Beginner | 1 | 1
1 | SQL Beginner | 2 |
1 | SQL Beginner | 3 | 3
2 | SQL Advanced | 1 |
2 | SQL Advanced | 2 |
2 | SQL Advanced | 3 | 3
(6 rows)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°