Query using Where exists and correlated query [Resolved]

Posted by Sriharim under Sql Server on 6/10/2015 | Points: 10 | Views : 478 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved 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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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 Kumaraspcode2009@gmail.com Sriharim, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

0	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

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

0	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

0	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

0	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

Latest Posts