Query using Where exists and correlated query [Resolved]

Posted by Sriharim under Sql Server on 6/10/2015 | Points: 10 | Views : 406 | Status : [Member] | Replies : 6
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



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