Re: How do I do this select?

select distinct user.userId, fu.UserID as FriendID, fu.Name as FriendName from user, friendlist as f, user as fu where f.userkey = user.userkey and f.friendkey = fu.userkey order by user.userId, friendId; "Chris W" <1qazse4@cox.net> wrote in message news:40015A03.2060505@cox.net... > I sent this to the mysql list but it doesn't seem to be working so I was > hoping someone here could help. > > I have the following 2 tables. > > CREATE TABLE User ( > UserKey INT NOT NULL AUTO_INCREMENT, > UserID CHAR(16) NOT NULL UNIQUE , > Name VARCHAR(20), > PRIMARY KEY (UserKey) > ); > > CREATE TABLE FriendList( > UserKey INT NOT NULL, > FriendKey INT NOT NULL, > PRIMARY KEY (UserKey, FriendKey) > ); > > The second table is a many to many relationship table. I want to select > every row from FriendList and link it to userID so the out put looks > something like this. > > UserID FriendID FriendName > a x x-name > a y y-name > a z z-name > b a a-name > b c c-name > b z z-name > c b b-name > c x x-name > > > This will give me sort of what I am looking for, but it shows the > UserKey in the first column and I want the UserID. I have tried a few > others but just got errors. > > SELECT f.UserKey, UserID FriendID, Name FriendName > FROM User u, FriendList f > WHERE f.FriendKey = u.UserKey > ORDER BY f.UserKey, FriendID;