SQL can not refer table in from clause within where-CodePudding

I have a simple SQL query used in oracle 11g

select something 
from sc 
where sc.column satisfies something

but I can not refer SC in the where composite, any one can help explain this, thanks in advance.

---second edit: I tested the sql command in oracle 19c it works but in 11g it does not work.

table contents

create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);

example data

insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');

sql command

SELECT SNO
FROM SC A
WHERE 0 = (SELECT COUNT(*) 
           FROM 
               (SELECT B.CNO 
                FROM SC B
                WHERE B.SNO = 's001'
                MINUS
                SELECT C.CNO 
                FROM SC C
                WHERE A.SNO = C.SNO) --this is the error location, oracle reports invalid identifier A.
          );

CodePudding user response：

I would try a exists clause instead of where 0 =

SELECT SNO
FROM SC A
WHERE not exists (SELECT B.CNO 
                FROM SC B
                WHERE B.SNO = 's001'
                MINUS
                SELECT C.CNO 
                FROM SC C
                WHERE A.SNO = C.SNO) --this is the error location, oracle reports undefined A.

I'm assuming you want to return where there is not a match?

CodePudding user response：

If you're trying to get the CNO that are not only in SNO 's001'.

Then maybe this.

SELECT DISTINCT SNO, cno
FROM SC sc
WHERE SNO = 's001'
  AND EXISTS (
        SELECT 1 
        FROM SC sc2
        WHERE sc2.CNO = sc.CNO
          AND sc2.SNO != sc.SNO
      )