join delivered table with itself-CodePudding

There are such tables

create table a (id int,pid int);
insert into a values
(1, NULL),
(2,1),
(3,5);

create table b (aid int);
insert into b values
(1),
(2),
(3);

and a query

select * 
from 
  (select distinct aid as id 
     from b
  ) as t
left join a using(id)

The result is

id  pid
1   (null)
2   1
3   5

I want to remove records which pid are present in the table t as id. In the case - the record with id 2

I tried to use

where not exists (select id from t where t.id = a.pid)

and

left join t t2 on t2.id = a.pid

but in both cases get error 1146 - Table does not exist because t is delivered table

Hope to do that in one query. Any help would be appreciated

sqlfiddle

CodePudding user response：

MySQL is of course correct t doesn't exist, but as t is derived from b and you only want to see of there is any aid identical you should use that instead,

so you get

select * 
from 
  (select distinct aid as id 
     from b
  ) as t
left join a using(id)
where not exists (select NULL from b where b.aid = a.pid)

See example http://sqlfiddle.com/#!9/9bf8666/14