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 |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2004-07-20 : 11:59:01
|
| Hi friends, Iam Currently pulling from Dept_Details by checking the entries for Prof_first_name, prof_last_name,asst_prof_first_name and asst_prof_last_name.If there is an entry for a asst_prof then breaking a single row into 2 rows where both the entries prof and asst prof_will be entered with corressponding instructor_type in Dept_Details table.ref_id determines which asst_prof is under which prof.This query works fine.I was thinking is there any other alternative for this query as far as the breaking down and then joining them is considered.-- Prof Query -- instructor_type =1SELECT R.ref_id,1 AS instructor_type,D.prof_first_name as first_name ,D.prof_last_name AS last_name, P.tel_num AS home_phone,D.billing_addr AS mailing_address,D.billing_city AS mailing_city, D.billing_state AS mailing_state,D.billing_zip AS mailing_zipcode,D.ssn AS ssn_no, P.w_tele AS work_phone,getdate() as create_date,'Admin' as created_byFROM Dept_Details D INNER JOIN referral_details R ON D.emp_id = R.emp_id LEFT OUTER JOIN phone_details P ON P.emp_id= R.emp_idUNION-- asst_prof Query-- instructor_type =2SELECT R.ref_id,1 AS instructor_type,D.asst_prof_first_name as first_name ,D.asst_prof_last_name AS last_name, P.tel_num AS home_phone,D.billing_addr AS mailing_address,D.billing_city AS mailing_city, D.billing_state AS mailing_state,D.billing_zip AS mailing_zipcode,D.ssn AS ssn_no, P.w_tele AS work_phone,getdate() as create_date,'Admin' as created_byDept_Details D INNER JOIN referral_details R ON D.emp_id = R.emp_id AND D.asst_prof_first_name IS NOT NULL AND D.asst_prof_last_name IS NOT NULL AND D.asst_prof_first_name<>'' AND D.asst_prof_last_name<>'' LEFT OUTER JOIN phone_details P ON P.emp_id= R.emp_id ORDER BY R.referral_id |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-20 : 19:45:21
|
| Well, not knowing anything more about your table structure, I'd have to say you probably have a good solution here, except, shouldn't the second query have 2 AS instructor_type?BTW, it sure seems like your Department table should be restructured so that the Prof and Assistant Prof are held in a separate table such as 'Person' and then related back to their Department. I mean, isn't there a one-to-many relationship there? Don't departments have more than just one professor and one assistant professor? And isn't it possible that a professor or assistant works in more than one department (which really would be a many-to-many relationship)?-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
 |
|
|
|
|
|
|
|
Breaking 1 row to 2