Sql-server – Nested SQL query made it hard to understand, what’s going on?

MySQLsql server

Tables:

employee(eno,ename)

works_on(eno,pno,ename)

project(pno, project)

select ename from employee where eno in(
select eno from works_on where pno =(select * from project))

what is the output ?

a)Employee who works on all project.

b)Employee who works one project .

c) name of employee who works on more than one project.

Best Answer

As it stands this query:

select ename from employee where eno in(
select eno from works_on where pno =(select * from project))

Will return a 116 Error in SQL Server.

Msg 116, Level 16, State 1. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Due to having a subquery with SELECT *, in the where clause. If you ammend this by selecting the PNO column from project.

select ename from employee where eno in(
select eno from works_on where pno = (select pno from project))

You will still (potentially) get an error depending on your actual dataset.

Msg 512, Level 16, State 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Assuming you intend to use the query as followed:

select ename from employee where eno in(
select eno from works_on where pno in (select pno from project))

You will get a list of employees that work on (a) project(s).