February 19, 2008 at 4:58 am
I have two tables I am trying to join. One table, tItem, contains action items. The relevant columns are ItemID, Owner, Manager and Requestor. For tPerson, the second table, relevant columns are PersonID, FirstName and LastName. tPerson table contains a list of personnel on the project. Owner, Manager and Requestor are tinyint columns that contain an ID that is a foreign key to PersonID column in tPerson. Given the current table structure (not my design), is the following query the best way to produce a row with the ItemID and names of the owner, manager and requestor?
select ItemID,
(tp.firstname + ' ' + tp.lastname) as owner,
(tp1.firstname + ' ' + tp1.lastname) as manager,
(tp2.firstname + ' ' + tp2.lastname) as requestor,
WBSdescription,
--Comments,
*
from tItem ta inner join tPersonnel tp on tp.personID = ta.owner
inner join tPersonnel tp1 on tp1.personID = ta.manager
inner join tPersonnel tp2 on tp2.personID = ta.requestor
Also, would a better design to be to pull the owner, manager and requestor out of the ItemID table into another table? Thanks for your help.
February 19, 2008 at 5:29 am
Certainly nothing wrong with that approach.
If you did remove owner, manager and requestor into another table, then you would essentially be creating a one-to-one relationship between that tuple and the tltem table, and probably gives you no benefit.
February 19, 2008 at 2:14 pm
Thanks for your input.
February 19, 2008 at 3:53 pm
lfmn (2/19/2008)
I have two tables I am trying to join. One table, tItem, contains action items. The relevant columns are ItemID, Owner, Manager and Requestor. For tPerson, the second table, relevant columns are PersonID, FirstName and LastName. tPerson table contains a list of personnel on the project. Owner, Manager and Requestor are tinyint columns that contain an ID that is a foreign key to PersonID column in tPerson. Given the current table structure (not my design), is the following query the best way to produce a row with the ItemID and names of the owner, manager and requestor?select ItemID,
(tp.firstname + ' ' + tp.lastname) as owner,
(tp1.firstname + ' ' + tp1.lastname) as manager,
(tp2.firstname + ' ' + tp2.lastname) as requestor,
WBSdescription,
--Comments,
*
from tItem ta inner join tPersonnel tp on tp.personID = ta.owner
inner join tPersonnel tp1 on tp1.personID = ta.manager
inner join tPersonnel tp2 on tp2.personID = ta.requestor
Also, would a better design to be to pull the owner, manager and requestor out of the ItemID table into another table? Thanks for your help.
imho, either rename tItem.owner, .manager, and .requestor to .ownerID, .managerID, and .requestorID or rename the concatenated name results. you should never rename a result column with the name of an existing column since it could lead to confusion especially if someone else has to utilize or maintain your code. (in this query .owner = name of the owner while in a raw select or other query .owner = a number.)
i don't know if the * in your query is actual code, but if it is you'll wind up with all the columns in tPersonnel table 3 times and no way to distinguish them. if .owner, .manager, or .requestor are optional (nullable) you should use left outer joins to tPersonnel.
lastly, with a tinyint your design can only support 256 persons.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply