CODEWITHSUNDEEP
sqlsql-server-2008
user5618269
user5618269

Reputation: 31

How to resolve subquery by using join condition

I have a emp table columns as deptno,ename and i have dept table columns as deptno,dname. In emp table

Deptno   ename              
10       pavan
20       rav
30       kah
40       pab

IN DEPT TABLE

deptno   dname
10       sales
20       marketing
30       research
40       sales

Q) Find ename of any employee in the same department name as pavan. How to write subquery by using join condition?

Upvotes: 0

Views: 89

Answers (2)

minatverma
minatverma

Reputation: 1099

Try this :

select ename from 
emp e
where e.Deptno = 
(select deptno from emp 
where ename='pavan')

SQL Fiddle demo

http://sqlfiddle.com/#!3/867b3/12

Upvotes: 1

Bala
Bala

Reputation: 704

It's always good to use JOIN instead of Sub-query. I guess, the question is to use a JOIN without using a Sub-query.

Please execute the below query:

SELECT
    E1.Deptno,
    E2.ename 
FROM Employees E1
    INNER JOIN Employees E2 ON E2.Deptno = E1.Deptno
WHERE
    E1.ename='pavan'

Upvotes: 0

Related Questions