Re: Tricky join question

Tim Tassonis <timtas@cubic.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°