我有两个 SQL Server 表:CHANNELS
& SUBSCRIBERS
,我想从中获取行CHANNELS
不存在于SUBSCRIBERS
在某种条件下。
我尝试过INNER
和OUTER(LEFT) JOIN
但这对我不起作用,他们都给了我相同的答案,这不是我想要的。
这些表是这样的:
CHANNELS table:
CREATE TABLE CHANNELS
(
ChanID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,
name varchar(30) NOT NULL,
type varchar(10) NOT NULL,
description varchar(500) NOT NULL,
nickname varchar(20) NOT NULL
REFERENCES USERS(nickname),
subscribersnum int NOT NULL
)
订阅者 table:
CREATE TABLE SUBSCRIBERS
(
nickname varchar(20) NOT NULL REFERENCES USERS(nickname),
ChanID int NOT NULL REFERENCES CHANNELS(ChanID)
)
外键nickname
取自其他一些名为USERS
假设在CHANNELS我们有以下几行:
CHanID | type | name | description | (createdby) | subscribersnum
| | | | nickname |
-------------------------------------------------------------------------
1 public first hi Matty 3
2 public second hii Rose 2
3 private third hiii Matty 2
4 public forth hiiii Lucy 5
and in SUBSCRIBERS
我们有:
nickname | ChanID
------------------------
Jonny 1
Matty 3
Jonny 4
Rose 3
Rose 2
我想选择所有public昵称“Jonny”的用户尚未订阅的频道 ID 和名称,因此在上面的示例中我希望查询返回两行:
ChanId | name
-----------------
2 second
我所做的查询如下:
SELECT
CHANNELS.ChanID
FROM
CHANNELS
LEFT JOIN
SUBSCRIBERS ON (CHANNELS.type = 'public'
AND SUBSCRIBERS.nickname = 'Jonny'
AND CHANNELS.ChanID != SUBSCRIBERS.ChanID);
SELECT
CHANNELS.ChanID
FROM
CHANNELS
INNER JOIN
SUBSCRIBERS ON (CHANNELS.type = 'public'
AND SUBSCRIBERS.nickname = 'Jonny'
AND CHANNELS.ChanID != SUBSCRIBERS.ChanID);
但他们没有做我需要的事情。
有什么建议么?
Thanks