SEO

#1 (**permalink**) 02-28-2008, 06:50 AM

hi

i am experimenting with sql for getting lists of friends.

select friend.*
from user
left join link on user.id=link.id and user.id = $MYID
right join user friend on link.friend_id = friend.id
where user.name is null;

on my local windows machine running mysql 5 it works fine, however
when i try with to import the tables to a unix machine running mysql
4, the select fails.

- if i do "user.name is null", i get an empty set.

- if i do "user.name is not null", i get 10000 rows, where each of the
100 rows from the user table is repeated 100 times each.

(the error might not even be related to mysql 4/5, but i am at a loss here)

does anyone know how to resolve this?

#2 (**permalink**) 02-28-2008, 06:50 AM

> hi
>
> i am experimenting with sql for getting lists of friends.
>
> select friend.*
> from user
> left join link on user.id=link.id and user.id = $MYID
> right join user friend on link.friend_id = friend.id
> where user.name is null;
>
> on my local windows machine running mysql 5 it works fine, however
> when i try with to import the tables to a unix machine running mysql
> 4, the select fails.
>
> - if i do "user.name is null", i get an empty set.
>
> - if i do "user.name is not null", i get 10000 rows, where each of the
> 100 rows from the user table is repeated 100 times each.
>
> (the error might not even be related to mysql 4/5, but i am at a
> loss here)
>
> does anyone know how to resolve this?
>

It won't help that you've got two table listed after the right join
statement. I think

right join user friend on ... should be
right join friend on ...

Edward

#3 (**permalink**) 02-28-2008, 06:50 AM

some more info on this:

putting "user AS friend" improves the query, but does not eliminate the problem.

also, here are the version numbers for each sql server:

mysql Ver 12.22 Distrib 4.0.22, for portbld-freebsd4.9 (i386)
mysql.exe Ver 14.12 Distrib 5.0.24, for Win32 (ia32)

the query even runs fine on a related bsd machine with mysql 5, so the
problem is quite certain to be mysql 4.

hope someone can help!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

left join, right join failure with mysql4