Re: Tricky join question - Mailing list pgsql-general

Tim Tassonis wrote:
> 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
>

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.

b