Select statement with multiple joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select statement with multiple joins

Author

Topic

femig
Starting Member

5 Posts

Posted - 2006-03-15 : 05:28:19

i have a select statement which joins several tables together..
i have a main table joined to several lookup tables AND joined back to the main table (manager_id)

the problem is that if i don't have a manager_id, the elect statement returns no rows..even though i know there is data for that primary key in my main table..
can someone please show me how to rewrite the select statement?
-------------------------
select s.*,a.*,a.cost_center, a.emp_status, a.employee_num, a.title, a.first_name,c.department, a.middle_name, a.surname, a.email, a.date_of_birth, b.position_title,a.start_date,man.first_name as man_first_name,man.surname as an_surname,d.office_location,man.email as man_email from employee_data a,employee_data man,
position_lookup b, department_lookup c,office_location d, states_lookup s
where a.current_position_id=b.position_id
and d.id=a.location_id

and man.usercode=a.manager_id

and a.state_of_origin_id = s.state_id

and a.dept_id=c.dep_id

and a.usercode = @usercode
-----------------------------

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-15 : 05:45:55

can you post your table DDL & sample data ?

----------------------------------
'KH'

ourspt
Starting Member

33 Posts

Posted - 2006-03-15 : 05:46:35

Not sure what the effect on efficiency is but you may want to try this:

select s.*,a.*,a.cost_center, a.emp_status, a.employee_num, a.title, a.first_name,c.department, a.middle_name,
a.surname, a.email, a.date_of_birth, b.position_title,a.start_date,man.first_name as man_first_name,
man.surname as an_surname,d.office_location,man.email as man_email
from employee_data a,
position_lookup b, department_lookup c,office_location d, states_lookup s
where a.current_position_id=b.position_id
and d.id=a.location_id
--and man.usercode=a.manager_id
and exists (select 1 from employee_data where usercode = a.manager_id)
and a.state_of_origin_id = s.state_id
and a.dept_id=c.dep_id
and a.usercode = @usercode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources