Re: Tricky join question

[Andreas Kretschmer <akretschmer(at)spamfence(dot)net>]

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°