带有两个连接和一个简单逻辑的 sql 查询 [英] sql query with two joins and a simple logic

问题描述

请找到小提琴http://sqlfiddle.com/#!9/4a4aff/7

Friends_list 表格

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

用户表

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

从这两个表中，我想列出所有用户，而没有在好友列表中状态为 P 或 B 的用户，使用连接似乎很简单，但在 wordpress 中，此代码不起作用，查询如下.

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;

小提琴

编辑

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

这篇关于带有两个连接和一个简单逻辑的 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！