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

Question

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')

Answer 1

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