I have two tables: a candidate table with candidate ID as a main key and the second table is one of educations linking candidate ID with the school they went to.

I want to filter schools where there are 50 or more candidates from that school. I also want the candidate names too.

select candidates.first_name, candidates.last_name
from candidates
where candidates.id IN (select e.candidate_id, e.school_name, count(e.school_name)
                        from educations e
                        group by e.candidate_id, e.school_name
                        having count(e.school_name) >= 50)

I'm getting an error that says:

Subquery has too many columns

CodePudding user response：

When you are using a subquery inside an IN condition, your subquery can only return a single column.

CodePudding user response：

As Stu already said in the coment, a EXISTS would be faster than an IN clause

In ana IN your subselect only can return so many columns as a defined by the column name(s) before the IN

This example of a query is for MySQL, but it should work on any Databse system and of course is simplified

CREATE tABLE candidates (id int, first_name varchar(10), last_name varchar(10))

INSERT INTO candidates VALUEs(1,'a','an'),(2,'b','bn')

Records: 2  Duplicates: 0  Warnings: 0

create TablE educations (id int, candidate_id int,school_name varchar(10))

INSERT INTO educations VALUES (1,1,'school A'),(2,1,'school B'),(3,1,'school C'),(4,1,'school D')
  ,(5,1,'school E'),(6,2,'school A'),(7,2,'school B'),(9,2,'school C')

Records: 8  Duplicates: 0  Warnings: 0

select candidates.first_name, candidates.last_name
from candidates
where EXISTS (select 1
                        from educations e
                        WHERE e.candidate_id = candidates.id 
                        having count(e.school_name) >= 5)

fiddle