"Mike Collins" wrote:

> In the following query, I will be making 6 joins for each ID in the Problems
> table to get a person's full name from our personnel table. Is there another
> 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" <MikeColl***@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:
>
>> In the following query, I will be making 6 joins for each ID in the
>> Problems
>> table to get a person's full name from our personnel table. Is there
>> another
>> 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
>> ...

"Rick Sawtell" wrote:

>
> "Mike Collins" <MikeColl***@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:
> >
> >> In the following query, I will be making 6 joins for each ID in the
> >> Problems
> >> table to get a person's full name from our personnel table. Is there
> >> another
> >> 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
> >> ...
>
>
> 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
>
>
>
>