First of all you want to show user data (name etc.), so select from user. You want to show their friends status related to user 1 with it, so join accordingly. As they can have a friends status related to user 1 or not, but you want to show them anyway, you cannot use an inner join, but need an outer join. At last exclude user 1 itself from the results in the where clause.
select u.*, f.status
from user u
left join friends f on f.friend = u.id and f.user = 1
where u.id <> 1
EDIT (after acceptance): If you want all users matching certain criteria (say firstname like ‘%e%’), then your results would have to contain that user, too, to give sense to the data shown. So you would have to query the user table twice. In order to do that, I would first combine all users matching that criteria with all others (i.e. other user ID), and then left join the friend relation.
select user_self.*, user_other.*, f.status
from user user_self
join user user_other on user_other.id <> user_self.id
left outer join friends f on and f.user = user_self.id and f.friend = user_other.id
where user_self.firstname like '%e%';
Remove the WHERE clause if you want all users with all relations.
3
solved How can i get results from two MySql tables [closed]