Select All using SQL subquery

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 by msanz for Select All using SQL subquery

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'  

Answer by Mani for Select All using SQL subquery

You can't put two columns in subquery

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

Answer by Echarnus for Select All using SQL subquery

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

You need to remove the name in the subquery. When executing a query, think as each value received by the subquery will be compared with the value of your 'main' query.

If you have the following record in employees

ID   Name   Sal   Dno  1    Bob    3000     3  2    Alice  3000     2  

And the following record in Department

Dno Name            1  Advertisement   2  Programming     3  Analysement  

You can't of course compare the value 3 of Bob with {3, Analysement}. Subqueries are usually used for filtering your rows, not for wanting to display extra values as a join can do.

Answer by Unnikrishnan R for Select All using SQL subquery

Better to use 'EXISTS' instead of 'IN' clause. Here is the script

SELECT Name, Sal  FROM Employee e  WHERE Name= 'aa'      AND EXISTS (SELECT 1 FROM Department d WHERE e.Dno=d.Dno)   

Answer by Harshil for Select All using SQL subquery

Subquery can be used to fetch data from multiple RELATED tables. It can't be used to join two tables. If u want to show multiple tables, you either have to use JOIN or UNION.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72