Re: Tricky join question

Thanks to you all for your replies. I was able to solve my problem after
some more reading in the manual:


select c.id, c.name, pc.person_id
from person as p
      cross join course as c
left outer join person_course as pc
      on  (p.id = pc.person_id and c.id = pc.course_id)
where p.id = 2;


A few remarks to the answers:

I seem to have been misunderstood in a way that people understood that I
implied that mysql is right and postgres is wrong. This was in no way my
opinion. I just reported what results I got under mysql. I very much
prefer Postgres over mysql and never questioned postgres' correctness.

To brian:

>>> Please, read more about referential integrity.
>>
>>
>> Thanks, but I already have read a lot about it 14 years ago.
>>
>> Bye
>> Tim
>>
>
> In all your long years of experience, perhaps you haven't come across this?
>
> http://catb.org/~esr/faqs/smart-questions.html
>
> If you're going to ask a question here the least you could do is meet us half-way.
>

I think I asked quite politely, did not blame anybody else and just
described my problem. I agree, I felt a bit insulted when being told to
read about referential integrity, because that clearly had nothing to do
with my question and I do know about it. I'm coming from Oracle and not
from mysql.

And thanks Andreas, I just saw your latest post which has the same
solution as I got in it.

Bye
Tim