joins

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  October 6, 2005 at 1:22 pm

  #89575

  Hi

  I have an Employees table as

  EmpId EmpNo SupervisorId

  1 234 3

  2 236 5

  3 324 1

  Here supervisorId is an EmpId where it is acting as supervisor for that EmpId.

  I need empno's with a given EmpId and it shud by itself pickup the supervisordId and find the EmpNo for that EmpId.

  For example, if i am given with EmpId=1, i need to get Empno's as

  234

  324

  because for EmpId=1 we have SupervisorId=3 which is nothing but EmpId=3 and for that EmpId=3,we have Empno=324.

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  October 6, 2005 at 1:32 pm

  #595642

  SET NOCOUNT ON

  DECLARE @Employee TABLE

  (

  EmpId INT,

  EmpNo INT,

  SupervisorID INT

  )

  INSERT INTO @Employee

  SELECT 1, 234, 3 UNION

  SELECT 2, 236, 5 UNION

  SELECT 3, 324, 1

  SELECT A.EmpId, A.EmpNo, A.SupervisorID, B.EmpNo SupervisorNo

  FROM

   @Employee A

  LEFT JOIN

   @Employee B

  ON

   A.SupervisorID = B.EmpId

  Regards,
  gova

• Mike Levan

  SSChampion

  Points: 14201

  More actions

  October 7, 2005 at 6:43 am

  #595834

  Thanks, I got my query done.