Re: Tricky join question

Hi,

Without restriction you're getting:

On Fri, Dec 22, 2006 at 02:55:56PM +0100, Tim Tassonis wrote:
> +---+-----------------------------------------+------------+
> | 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         |
> +---+-----------------------------------------+------------+

There are no rows in the table with person_id=2, so PostgreSQL is
returning the correct result (no rows). Seems like a bug in MySQL.

> 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;

I think what you want is to apply to restriction on person earlier,
maybe:

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 and p.id = 2)
      right outer join course as c on pc.course_id = c.id
order by 1;

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.