hey guys,

i have a table structured like this

ID | user1 | user2 | status
1 | John | Michael | approved
2 | Lucie | John | approved
3 | Michael | Lucie | not_approved

i want to pull (user1 OR user2)->(whichever is NOT John) into expr Friends, where status=approved and user1 OR user2 = John

so results would be: Michael, Lucie

the point is it's a friends database, and on a user's profile page i want to pull all his approved friends, but my problem is his name may be in 1 of 2 columns, depending on which user initiated the friend request 

this may help you....

1    SELECT x.id, 
2      CASE WHEN x.user1 <> 'john' THEN x.user1 
3           WHEN x.user2 <> 'john' THEN x.user2
4           ELSE 'n/a'
5      END as Friend,
6      x.status
7    FROM ( 
8     SELECT id, user1, user2, status
9       FROM table1
10       WHERE status = 'approved' AND (user1 = john OR user2 = john)) x

worked! thanks so much! let me ask you a step further because i didn't ask this additional step because I figured I could get it myself once I had the first part. but i'm having problems.

i want to join the table profiles to this command, where "Friend" = profiles.username (because i want to pull an avatar column from that table that matches the friend)

so far i've got this:

1    SELECT CASE WHEN x.asker <> @username
2    THEN x.asker
3    WHEN x.other <> @username
4    THEN x.other
5    ELSE 'n/a'
6    END AS Friend, profiles.profilepic FROM
7    (SELECT fid, asker, other, status FROM friends
8    WHERE status = 'accepted' AND (asker = @username OR other = @username)) AS x INNER JOIN profiles ON x.asker = profiles.username 

 that works when it's the asker, but if the friend is in 'other' i get the wrong defaultpic (i get that of @username's)

i've tried switching the end to the following:

 INNER JOIN profiles ON x.Friend = profiles.username

or

INNER JOIN profiles ON Friend = profiles.username 

neither has worked. do you know what i can do? 

try this....

1    SELECT y.friend, p.profilepic
2    FROM (
3      SELECT CASE 
4        WHEN x.asker <> @username THEN x.asker
5        WHEN x.other <> @username THEN x.other
6        ELSE 'n/a'END AS Friend
7      FROM (
8        SELECT fid, asker, other, status 
9        FROM friends
10       WHERE status = 'accepted' 
11         AND (asker = @username OR other = @username)) AS x) as y
12   INNER JOIN profiles as p ON p.username = y.friend 
13   

tried, it's only returning 1 result (should be 2)

the one returning is when @username is in other... it's properly returning the asker and his profilepic

but the other accepted friends relation, where @username is in asker, is not returning

your previous code was successfully returning the 2 friends 

Ok, maybe I am missing something.... if I use the statement below with the data in the table on your first post, and a table that has a username and a picture I get the following:

1    SELECT        y.Friend, p.picture
2    FROM            (SELECT        CASE WHEN x.user1 <> @username THEN x.user1 WHEN x.user2 <> @username THEN x.user2 ELSE 'n/a' END AS Friend
3                              FROM            (SELECT        id, user1, user2, status
4                                                        FROM            Table1
5                                                        WHERE        (status = 'approved') AND (user1 = @username) OR
6                                                                                  (status = 'approved') AND (user2 = @username)) AS x) AS y INNER JOIN
7                             Table2 AS p ON p.username = y.Friend

If I query for John I get

Lucie, LuciePicture
Michael, MichaelPicture

If I query for Lucie I get

John, johnpicture
michael is not shown because it is not_approved

 If I query for Michael I get

John, johnpicturre
Lucie is not shown because it is not_approved

If I change the Michael/Lucie record to approved then

If I query for John I get the same result as above

if I query for Licie I get

Michael, MichaelPicture
John, JohnPicture

If I query for Michael I get

John, JohnPicture
Lucie, LuciePicture.

Which to me is the correct behavior... but like i said, maybe I am missing something...

 sorry you were right and it works after a re-copy/paste attempt. i must've messed with a character somewhere before.

thanks for your hard work! you're great 

Anytime...