Download 100% FREE Office Document APIs for .NET
DotNetFunda.com
Online: 918
Home > Forums > Sql Server >

Query using Where exists and correlated query [Resolved]

Posted by Sriharim under Sql Server on 6/10/2015 | Points: 10 | Views : 367 | Status : [Member] | Replies : 6
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
Employee table:
EmployeeID   Name   Departmentid
1            a      1
2            b      1
3            c      3


Department table:
Departmentid    Departmentname
1                IT
2                HR


Query to get the details Employeeid,name,departmentname using correlated query and where exists

---
Srihari

Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Bandi on: 6/11/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

SELECT *, ( SELECT Departmentname FROM Department WHERE e.Departmentid = Departmentid )as DeptName

FROM Employee e

WHERE EXISTS ( SELECT 1 FROM Department WHERE e.Departmentid = Departmentid)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 6/11/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

Refer this Both Query

Step1:
=========
Select * from ttf1 A INNER JOIN ttf2 B ON A.DEPARTMENTID=B.DEPARTMENTID

Step2:
=======
Declare @DeptId int
Set @DeptId=1
Select * from ttf1 A INNER JOIN ttf2 B ON A.DEPARTMENTID=B.DEPARTMENTID where A.DEPARTMENTID=@DeptId

Mark as Answer if its helpful to you

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
For your expected result, query doesn't require any EXISTS & correlated subqueries..

I can provide you better solution without using correlated sub queries if you want...

SELECT e.EmployeeID, E.Name, D.Departmentname
FROM Employee e
JOIN Department D ON e.Departmentid = D.Departmentid


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sriharim on: 6/13/2015 [Member] Starter | Points: 25

Up
0
Down
but they asked me to do with where exists

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Bandi on: 6/16/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I have posted reply on 11th June by using EXISTs.. check and let me know

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sriharim on: 6/16/2015 [Member] Starter | Points: 25

Up
0
Down
sorry for delay...thanks for ur post...ur reply marked

SELECT eid,FirstName, ( SELECT Department_name FROM Department WHERE did=e.did )as DeptName
FROM Employee e
WHERE EXISTS ( SELECT 1 FROM Department WHERE DID = e.DID)


---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts