It misses the data I'm looking for when I join 1

[adelante]

Hello!
I'm doing a small community project, and got a little problem with mySQL. I got a table for 'users', and a table 'usersfriends' with the user ID, and the mail address of the users friends.

I would like it to work similar to MSN: Your friends doesn't have to be users to be added to your list. You can add their email addresses first, so that they appear on your friendlist. And when they some day sign up, then their emailaddress-nickname will be replaced with the nickname they have used.

Now:

When I make the SELECT to find the mail addresses of the friends they all show up - great!
SELECT friendsmailaddress FROM usersfriends WHERE user='userID'

But when I make the SELECT to find the users friends AND at the same time want to get the nickname from those who are already signed up, I ONLY get the friends who are already signedup.
SELECT friendsmailaddress,nickname FROM usersfriends,user WHERE user='userID' AND user.nickname=usersfriends.friendsmailaddress

What I want to do is to get ALL the friends + the nicknames of those who has signed up. Is that possible, or do I have to make some clumbsy way, by making two SQL selects and then run the result through a perl script??

 

[r937]

all you need is an OUTER JOIN

SELECT F.friendsmailaddress
     , U.nickname 
  FROM usersfriends as F
LEFT OUTER
  JOIN user as U
    ON U.nickname = F.friendsmailaddress 
 WHERE F.user = 'userID'

r937.com | rudy.ca

 

[adelante]

Is it possible to make a "double join"??

I got the 2 tables: the 'user' table, and the 'usersfriends' table where all the friends of the user is.

In the 'user' table you set your status like: online/busy/invisible and so on.

I thought of this old feature seen in e.g. ICQ "always visible". it requires a double join I guess though.

First find all the friends in the 'usersfriends' table, then join to the 'user' table and BACK to 'usersfriends' to see if THEY have set user to 'always visible' so that an 'invisible' status can get overruled.

I have spend all day on this, but keep getting weird looking results or errors.