Post reply

Join Query

  • 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

  • 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

  • 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 !!!**

  • August 31, 2005 at 6:46 am

    #586434

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

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply