RE: left join, right join failure with mysql4

[Edward Kay]

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: left join, right join failure with mysql4

[Olav Mørkrid]

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!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]