Nilani Algiriyage Nilani Algiriyage - 3 months ago 11
SQL Question

Select All using SQL subquery

I have two tables like this

create table department(
Dno int primary key,
name varchar(50) not null
)


create table employee(
empid int primary key,
name varchar(50) not null,
sal decimal(7,2) not null,
dno int foreign key references department(Dno))


I want to get the same result of the following query using a SQL subquery.

SELECT e.Name, e.Sal, d.Dno, d.Name
FROM Employee e,
Department d
WHERE e.Dno = d.Dno
AND e.name = 'aa'


Following is what I tried.

SELECT Name, Sal
FROM Employee
WHERE Name= 'aa' AND Dno IN
(SELECT Dno, name
FROM Department)


This gives following error.


Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.

Answer

Your subquery should only select a single column and return a list.

SELECT Name, Sal
FROM Employee
WHERE Name= 'aa' AND Dno IN
    (SELECT Dno FROM Department)

On the other hand, you cannot get columns from Department into the result by making a subquery.

The best way to achieve what you're trying to get is by joining tables:

SELECT e.Name, e.Sal, d.Dno, d.Name
FROM Employee e 
INNER JOIN Department d ON e.Dno = d.Dno
WHERE e.name = 'aa'
Comments