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(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.