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.
| Author |
Topic |
|
jubinjose
Starting Member
20 Posts |
 Posted - 2006-04-14 : 11:22:01
|
| I have 3 tables1) tbl_dept1fields - dept1_key(primary key), dept1_description, user_id2) tbl_dept2fields - dept2_key(primary key), dept2_description, dept1_key(foreign key to dept1_key of tbl_dept1)3) tbl_dept3fields - dept3_key(primary key), dept3_description, dept2_key(foreign key to dept2_key of tbl_dept2)The idea is to store a hierarchy. Like a company might have northwest as dept1. HR, finance etc as dept2 and manager,sales etc as dept3Fourth table is tbl_user_to_department_relation which has fieldsrelation_key(primary), dept_key, dept_levelthe dept_key will be any of dept1_key, dept2_key or dept3_key. If its a dept1_key then dept_level=1. If it is dept2_key then dept_level=2 etcIdea is to attach a user to any department of a company(at a higher level or lower)I need a query that accepts user_id as param and returns all departments to which the user belongs in the following formatuserid, dept1_key, dept1_desc, dept2_key,dept2_desc, dept3_key,dept3_descIf user is attached to a level 2 department, then i expect that row to come out with null values for dept3_key and dept3_descsimilarly if user is attached to level1 department then i expect null for dept2_key,dept2_desc,dept3_key,dept3_descI wrote a query for this but I'm not sure if the union query I used there is the best way to do this (especially considering the fact that the data is going to be real huge and good response time is critical to the app). Would appreciate if someone can suggest a query to accomplish the samedept1 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Help with query