sql查询有两个连接和一个简单的逻辑

Please find the fiddle http://sqlfiddle.com/#!9/4a4aff/7

Friends_list Table

 | id | user_id | Friend_id | status
 |  1 |   1     |    24     |   P
 |  2 |   18    |    26     |   P

User Table

| ID | email   | password  |  ..N
 |  1 |   xx    |    xx     |   xx
 |  2 |   xx    |    xx     |   xx

from those both table i want to get all the users listed without the users whose status was P or B in friends list table it seems to be simple using joins but in wordpress this code is not working and the query is as below.

select DISTINCT u.ID, u.user_nicename from wp_users u
LEFT JOIN friends_list f ON f.friend_id=u.ID
WHERE f.status  NOT IN ('P', 'B')

Change your query as below. If you only want data from first table then no need join query only use inner query

select DISTINCT u.ID, u.user_nicename from wp_users u WHERE u.ID not in 
(select f.friend_id from friends_list f where f.status IN ('P', 'B')) order by u.ID;

Fiddle

EDIT

select DISTINCT u.ID, u.user_nicename from wp_users u
LEFT JOIN friends_list f ON f.friend_id=u.ID
WHERE f.status NOT IN ('P', 'B') OR f.status IS NULL ORDER BY u.ID

Just change the binding column like this :-

select DISTINCT u.ID, u.user_nicename from wp_users u
LEFT JOIN friends_list f ON f.user_id=u.ID
WHERE f.status NOT IN ('P', 'B');

You must join tables with user id like below:

LEFT JOIN friends_list f ON f.user_id=u.ID

select DISTINCT u.ID, u.user_nicename from wp_users u
    LEFT JOIN friends_list f ON f.user_id=u.ID
    WHERE f.status  NOT IN ('P', 'B')

Add two records at friends_list table with Q and Z status for testing

INSERT INTO `friends_list` (`id`, `user_id`, `Friend_id`, `status`) VALUES
(80, 1, 26, 'P'),
(79, 18, 26, 'P'),
(78, 1, 24, 'P'),
(79, 20, 24, 'Q'),
(80, 21, 24, 'Z');

sqlfiddle working example

From what I understand you want ALL user except the those who has P or B as status. To get those users that don't have a value in the friends_list table at all you can add a null check to your query.

select DISTINCT u.ID, u.user_nicename from wp_users u
LEFT JOIN friends_list f ON f.friend_id=u.ID
WHERE f.status NOT IN ('P', 'B') OR f.status IS NULL

I'm also unsure if you're joining on the right column? Should it be user_id instead of friend_id?

You can use this query, your fiddle is showing blank result as you have only data with P Status, try to add one more entry with some other status then you will get the result.

select DISTINCT u.ID, u.user_nicename from wp_users u left join friends_list f 
on f.friend_id=u.ID where f.status is NULL