CODEWITHSUNDEEP
sqlpostgresql
lel
lel

Reputation: 41

SQL - using ANSI standard

My below query using two select statements works:

create or replace view q2 as
select count(p.id) nstudents,
(select count(p.id) nstaff
from people p, staff s where p.id = s.id)
from people p, students s where p.id = s.id;

but when i include a third sub query:

create or replace view q2 as
select count(p.id) nstudents,
(select count(p.id) nstaff,
(select count(p.id) nboth
from people p, students s, staff t where p. id = s.id and p.id = t.id)
from people p, staff t where p.id = t.id)
from people p, students s where p.id = s.id;

it gives me the following error:

ERROR:  subquery must return only one column
LINE 3: (select count(p.id) nstaff,

Am i making some mistake while including a third query or is there a limit of just 2 nested select statements?

Upvotes: 0

Views: 108

Answers (1)

jpw
jpw

Reputation: 44881

The version with the additional sub-query fails because you insert the second sub-query into the first, when it should come after it like this:

select count(p.id) nstudents,
(select count(p.id) from people p, staff t where p.id = t.id) nstaff ,
(select count(p.id) from people p, students s, staff t where p. id = s.id and p.id = t.id) nboth 
from people p, students s where p.id = s.id;

However, the query could just as well be written using conditional aggregation (and to use explicit ANSI standard joins) like this: 

select 
    sum(case when s.id is not null then 1 end) nstudents,
    sum(case when t.id is not null then 1 end) nstaff,
    sum(case when s.id is not null and t.id is not null then 1 end) nboth
from people p
left join students s on p. id = s.id  
left join staff t on p.id = t.id

Upvotes: 2

Related Questions