Error in using WITH clause and INTERSECT in SQLite-CodePudding

I have two SQL queries; First one is:

with b as (select person_id from people where name='Ward Bond' and born=1903)
select title_id from b natural join crew;

Which is producing correct results and is OK. Another one is:

with c as (select person_id from people where name='John Wayne' and born=1907)
select title_id from c natural join crew;

Which is also totally OK and producing correct results. As soon as I try to find the intersection of these two queries using the following query:

with b as (select person_id from people where name='Ward Bond' and born=1903) select title_id from b natural join crew 
intersect
with c as (select person_id from people where name='John Wayne' and born=1907) select title_id from c natural join crew;

I get the error Error: near "with": syntax error I'm using SQLite3. Can you please help me to find the problem? The thing I'm trying to get is straightforward; I want to have the intersection of these two temporary tables.

CodePudding user response：

This is the correct syntax for SQLite:

select * from (
  with b as (
    select person_id 
    from people 
    where name='Ward Bond' and born=1903
  ) 
  select title_id from b natural join crew
)
intersect
select * from (
  with c as (
    select person_id 
    from people 
    where name='John Wayne' and born=1907
  ) 
  select title_id from c natural join crew
);

Another way to get the intersected rows:

with cte(name, born) as (values ('Ward Bond', 1903), ('John Wayne', 1907))
select c.title_id 
from crew c natural join people p
where (p.name, p.born) in cte
group by c.title_id
having count(distinct p.person_id) = 2;