Additional Information

Also, some people do not have middle names, how would I write the query to
pull this out? Thanks again.
"Mike Collins" wrote:

> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick