Join Query

• Iain Wilson

  Say Hey Kid

  Points: 666

  More actions

  August 31, 2005 at 2:54 am

  #91008

  Hi all,

  I'm trying to write a query to do something really simple, but it's giving me a real headache, so maybe you guys might be able to help me out.  I've got two tables - employees and offices - which contain data about...well, company employees and our offices   What I want to do is be able to query both tables and draw back certain key bits of information.  I've written the following piece of TSQL and my problem is this - it does bring the employee information back fine but any information from the offices table is incorrect.  Rather than displaying the company and department revelant to the individual it lists ALL companies and departments.  I suspect I am performing the join incorrectly but I'm not sure what.

  Any help would be gratefully received,

  Iain

  select

  emp.work_email as Email_Key,

  emp.Rn_Descriptor as Personal_Name,

  emp.job_title as Personal_Job_Title,

  emp.job_function as Personal_Job_Type,

  emp.office_name as Personal_office,

  emp.region as Personal_region,

  emp.number_ as Employee_Number,

  offices.department as Personal_Department,

  offices.COMPANY as Company

  from employee emp inner join offices offices on emp.office_name = offices.office_name

• Smruti-255597

  Old Hand

  Points: 301

  More actions

  August 31, 2005 at 3:31 am

  #586387

  Hi Iain

  The tables and the query is wotrking perfectly.

  Since i have tried And i got result

  So if u can give some junk data then it will be easier to solve what is ur desired result

  Smruti

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  August 31, 2005 at 6:42 am

  #586429

  Iain - do you have a primary key on your Employees table - like an empID maybe - with a corresponding foreign key relationship in the Office table ?!

  That's the column you would have to do a join on..!!!

  
  
  
  
  
  
  **ASCII stupid question, get a stupid ANSI !!!**

• Iain Wilson

  Say Hey Kid

  Points: 666

  More actions

  August 31, 2005 at 6:46 am

  #586434

  Thanks guys - I'll give that whirl and let you know how I get on.

• Iain Wilson

  Say Hey Kid

  Points: 666

  More actions

  August 31, 2005 at 8:00 am

  #586467

  Hi guys,

  I've got it figured out.  I was joining on utterly the wrong thing (and the wrogn table for that matter *blush*).  The complete query is below. 

  Iain

  select

  employee.work_email as Email_Key,

  employee.Rn_Descriptor as Personal_Name,

  employee.job_title as Personal_Job_Title,

  man.work_email as manager_email,

  employee.job_function as Personal_Job_Type,

  employee.office_name as Personal_office,

  savills_department.department as Personal_Department,

  employee.region as Personal_region,

  employee.number_ as Employee_Number,

  savills_companies.Rn_Descriptor as company

  from employee employee, savills_department savills_department, savills_companies, employee man

  where employee.department  = savills_department.savills_department_id

  and

  employee.savills_company = savills_companies.savills_companies_id

  and

  employee.reports_to = man.employee_id