[SOLVED] query help

[ToonMariner]

Morning - forgot how to do this!!!

 

I have two tables 'users' and 'friends' friends hsa 2 fields userid and friendid when some requests to be a friend a row is inserted into friends with their id in the 'userid' field and the person they wish to be friends with in the friendid field. When the request is accepted another row is entered with the ids swapped round.

 

What I need to do is select the userdetails of users who are in 'userid' and my id is in the friend column BUT there is no record with my id in the userid column and their id in the friendid.

 

This is what I have so far...

 

SELECT `users`.* FROM `users`, `friends` AS `f1` LEFT JOIN `friends` AS `f2` ON (`f2`.`userid` = `f1`.`friendid`) WHERE `f1`.`friendid` = " . $_SESSION['userID']

this simply selects all the rows where friendid is my id...

 

any help greatfully appreciated

[bubblegum.anarchy]

Something like this:

 

SELECT the_friend.id, the_friend.name
FROM users AS person
    INNER JOIN friends AS friend_of_theres ON person.id = friend_of_theres.friendid 
    INNER JOIN users AS the_friend ON friend_of_theres.userid = the_friend.id
    LEFT JOIN friends AS friend_of_mine ON friend_of_theres.userid = friend_of_mine.friendid AND friend_of_mine.userid = person.id
WHERE person.id = $_SESSION['userID'] AND friend_of_mine.userid IS NULL

[Illusion]

Select * from Users where userid = Any(select userid from Friends where friendid="$_SESSION['userid']";

This will done the job for

select the userdetails of users who are in 'userid' and my id is in the friend column 

but I didn't get 

BUT there is no record with my id in the userid column and their id in the friendid. 

what do you mean by that do you want that condition to be in query.

 

 

[ToonMariner]

its ok bubblegum did the trick!