英文:
Not exists not working as per expectation in Amazon Redshift database
问题
我试图执行以下情景的“not exists”操作，但我收到了错误消息
> 由于内部错误，不支持这种类型的相关子查询模式
Table1
| pkey | name | dept |
|---|---|---|
| 123 | TONY | SALES |
| 456 | JACK | HR |
| 789 | CHRIS | IT |
Table2
| ipkey | pkey | src |
|---|---|---|
| 111 | 123 | A |
| 222 | 123 | B |
| 333 | 456 | A |
| 444 | 789 | A |
| 555 | 789 | C |
我的要求是不获取src为C的数据，并从table1和table2中获取其他详细信息。
尝试的SQL语句：
select * from table1 t1 inner join table2 t2 on (t1.pkey = t2.pkey)
where t1.src in ('A','B')
and not exists
(
select 1 from table2 t2i
where t1.pkey = t2i.pkey
and t2i.src = 'C'
)
期望的结果集：
| pkey | name | dept | ipkey | src |
|---|---|---|---|---|
| 123 | TONY | SALES | 111 | A |
| 123 | TONY | SALES | 222 | B |
| 456 | JACK | HR | 333 | A |
英文:
I am trying to do a not exists for the below scenario, But I am getting the error message
>This type of correlated subquery pattern is not supported due to internal error
Table1
| pkey | name | dept |
|---|---|---|
| 123 | TONY | SALES |
| 456 | JACK | HR |
| 789 | CHRIS | IT |
Table2
| ipkey | pkey | src |
|---|---|---|
| 111 | 123 | A |
| 222 | 123 | B |
| 333 | 456 | A |
| 444 | 789 | A |
| 555 | 789 | C |
My requirement is not to fetch the src C data, and fetch other details from table1 and table2.
Sql tried:
select * from table1 t1 inner join table2 t2 on (t1.pkey = t2.pkey)
where t1.src in ('A','B')
and not exists
(
select 1 from table2 t2i
where t1.pkey = t2i.pkey
and t2i.src = 'C'
)
Expecting result set:
| pkey | name | dept | ipkey | src |
|---|---|---|---|---|
| 123 | TONY | SALES | 111 | A |
| 123 | TONY | SALES | 222 | B |
| 456 | JACK | HR | 333 | A |
答案1
得分: 0
以下是翻译好的部分：
"You might not need to use the Not Exixts keyword for your use case."
创建表 tableName1：
CREATE TABLE tableName1
(
pkey INT,
name VARCHAR(512),
dept VARCHAR(512)
);
INSERT INTO tableName1 (pkey, name, dept) VALUES ('123', 'TONY', 'SALES');
INSERT INTO tableName1 (pkey, name, dept) VALUES ('456', 'JACK', 'HR');
INSERT INTO tableName1 (pkey, name, dept) VALUES ('789', 'CHRIS', 'IT');
创建表 tableName2：
CREATE TABLE tableName2
(
ipkey INT,
pkey INT,
src VARCHAR(512)
);
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('111', '123', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('222', '123', 'B');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('333', '456', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('444', '789', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('555', '789', 'C');
查询 #1：
SELECT * FROM tableName1 T1
INNER JOIN tableName2 T2 ON T2.pkey = T1.pkey
AND T2.pkey NOT IN (SELECT pkey FROM tableName2 WHERE src IN ('C'));
结果表格如下：
| pkey | name | dept | ipkey | pkey | src |
|---|---|---|---|---|---|
| 123 | TONY | SALES | 111 | 123 | A |
| 123 | TONY | SALES | 222 | 123 | B |
| 456 | JACK | HR | 333 | 456 | A |
英文:
You might not need to use the Not Exixts keyword for your use case.
CREATE TABLE tableName1
(
pkey INT,
name VARCHAR(512),
dept VARCHAR(512)
);
INSERT INTO tableName1 (pkey, name, dept) VALUES ('123', 'TONY', 'SALES');
INSERT INTO tableName1 (pkey, name, dept) VALUES ('456', 'JACK', 'HR');
INSERT INTO tableName1 (pkey, name, dept) VALUES ('789', 'CHRIS', 'IT');
CREATE TABLE tableName2
(
ipkey INT,
pkey INT,
src VARCHAR(512)
);
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('111', '123', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('222', '123', 'B');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('333', '456', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('444', '789', 'A');
INSERT INTO tableName2 (ipkey, pkey, src) VALUES ('555', '789', 'C');
Query #1
SELECT * FROM tableName1 T1
INNER JOIN tableName2 T2 ON T2.pkey = T1.pkey
AND T2.pkey NOT IN (SELECT pkey FROM tableName2 WHERE src IN ('C'));
| pkey | name | dept | ipkey | pkey | src |
|---|---|---|---|---|---|
| 123 | TONY | SALES | 111 | 123 | A |
| 123 | TONY | SALES | 222 | 123 | B |
| 456 | JACK | HR | 333 | 456 | A |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论