Generating Sql Query

• prachi-749169

  Grasshopper

  Points: 12

  More actions

  June 27, 2008 at 12:35 am

  #193260

  I have following tables:

  Employee(EmployeeID,Name,ReportTo)

  CompanyEmployee(PKId,EmployeeID,CompanyId)

  EmployeeProduct(PkId,EmployeeID,ProductID)

  Product(ProductID,ProductName)

  When EmployeeID is passed then sql query should return all the employees that report to him (i.e all hierarchy of employees below him)which are in either CompanyEmployee or EmployeeProduct or in both tables.Along with EmployeeName query should return CompanyName and ProductName

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  June 27, 2008 at 4:57 am

  #835251

  [font="Verdana"]You need self join in this case. Like:

  Select E.EmployeeId, E.Name, E.ReportTo, M.Name From Employee As E Inner Join Employee As M On E.ReportTo = M.EmployeeId.

  Also you can add the columns from other tables with proper joins.

  Let us know.

  Mahesh

  [/font]

  MH-09-AM-8694