> Using the following query, I get back three records. I should be getting back
> four records. If I run the query using only the fourth join by itself, I will
> see the record that is missing. How can I rewrite this query to get back the
> four records I know I am supposed to get back? I hope this enough because I
> do not have access to create the structure. Thanks for any help.
>
>
> SELECT DISTINCT p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName,
> p.MiddleName,
> p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> FROM TestRecords t
> LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID = p.PersonnelID
> LEFT OUTER JOIN Common.dbo.Personnel p2 ON t.ScreenerID = p2.PersonnelID
> LEFT OUTER JOIN Common.dbo.Personnel p3 ON t.SubjectMatterExpertID =
> p3.PersonnelID
> LEFT OUTER JOIN Common.dbo.Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> LEFT OUTER JOIN Common.dbo.Personnel p5 ON t.EnteredByID = p5.PersonnelID
> LEFT OUTER JOIN Common.dbo.Personnel p6 ON t.SurveyorID = p6.PersonnelID
> WHERE t.statusid = 1

"Hilarion" wrote:

> Mike Collins wrote:
> > Using the following query, I get back three records. I should be getting back
> > four records. If I run the query using only the fourth join by itself, I will
> > see the record that is missing. How can I rewrite this query to get back the
> > four records I know I am supposed to get back? I hope this enough because I
> > do not have access to create the structure. Thanks for any help.
> >
> >
> > SELECT DISTINCT p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName,
> > p.MiddleName,
> > p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> > FROM TestRecords t
> > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID = p.PersonnelID
> > LEFT OUTER JOIN Common.dbo.Personnel p2 ON t.ScreenerID = p2.PersonnelID
> > LEFT OUTER JOIN Common.dbo.Personnel p3 ON t.SubjectMatterExpertID =
> > p3.PersonnelID
> > LEFT OUTER JOIN Common.dbo.Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> > LEFT OUTER JOIN Common.dbo.Personnel p5 ON t.EnteredByID = p5.PersonnelID
> > LEFT OUTER JOIN Common.dbo.Personnel p6 ON t.SurveyorID = p6.PersonnelID
> > WHERE t.statusid = 1
>
>
> I do not see what are all those p2, p3, p4, p5 and p6 sources joined in for
> if you don't even select any data from them.
> This query should give exactly the same results as your one:
>
> SELECT DISTINCT
>   p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName, p.MiddleName,
>   p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> FROM TestRecords t
> LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID = p.PersonnelID
> WHERE t.statusid = 1
>
> If in the real query you are using (selcting) the data from the other
> sources, then post the real query (and the one "using only the fourth join
> by itself") - the problem probably lays in those.
> You may also try to compare those query results without DISTINCT
> clause - it may help you find the cause.
>
>
> Kamil 'Hilarion' Nowicki
>
> PS.: You really should try posting at leas brief description of "TestRecords"
>      table structure (and function) with some example data and also
>      of "Personnel" table (as above: structure, function and example data)
>      and results you get with those example data and results you wanted
>      to get instead.
>

"Mike Collins" <MikeColl***@discussions.microsoft.com> wrote in message
news:B85078DE-03C6-43D2-88EC-8357A874E813@microsoft.com...
> Hopefully this will help. It is too much to create the exact structure of
the
> live database, but this example shows exactly what I need to do.
>
> How can I write a query that will return a personnel record for each
record
> where that person's id is found in the TestEvals table, regardless which
> column it is found in? Plus I need it to return distinct rows.
>
> The second query below shows a name that does not show up in the first
> query. I am trying to figure out how to get that name in the second query
to
> show in my first query.
>
> Thanks in advance for the help.
>
> SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> FROM TestEvals t
> LEFT OUTER JOIN Personnel p ON t.OriginatorID = p.PersonnelID
> LEFT OUTER JOIN Personnel p2 ON t.ScreenerID = p2.PersonnelID
> LEFT OUTER JOIN Personnel p3 ON t.SubjectMatterExpertID = p3.PersonnelID
> LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> WHERE t.TestStatus = 1
>
>
> SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> FROM TestEvals t
> LEFT OUTER JOIN Personnel p ON t.SubjectMatterExpertID = p.PersonnelID
> LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> WHERE t.TestStatus = 1
>
> CREATE TABLE [Personnel] (
> [PersonnelID]  INT NOT NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [TestEvals] (
> [TestEvalID]  INT NOT NULL ,
> [OriginatorID] [INT] NOT NULL,
> [ScreenerID] [INT] NOT NULL,
> [SubjectMatterExpertID] [INT] NOT NULL,
> [TestDirectorID] [INT] NOT NULL,
> [EnteredByID] [INT] NOT NULL,
> [Comments] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TestStatus] [INT] NOT NULL,
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [TestEvals]
>

> [TestStatus])VALUES('1','3','2','1', '4', '5', 'Comments13', '1')
>
>
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('1','Smith')
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('2','Johnson')
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('3','Collette')
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('4','Tyson')
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('5','Raymond')
> INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('6','Anderson')
>
> "Hilarion" wrote:
>
> > Mike Collins wrote:
> > > Using the following query, I get back three records. I should be
getting back
> > > four records. If I run the query using only the fourth join by itself,
I will
> > > see the record that is missing. How can I rewrite this query to get
back the
> > > four records I know I am supposed to get back? I hope this enough
because I
> > > do not have access to create the structure. Thanks for any help.
> > >
> > >
> > > SELECT DISTINCT p.PersonnelID, p.OrganizationID, p.TitleID,
p.FirstName,
> > > p.MiddleName,
> > > p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> > > FROM TestRecords t
> > > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID =
p.PersonnelID
> > > LEFT OUTER JOIN Common.dbo.Personnel p2 ON t.ScreenerID =
p2.PersonnelID
> > > LEFT OUTER JOIN Common.dbo.Personnel p3 ON t.SubjectMatterExpertID =
> > > p3.PersonnelID
> > > LEFT OUTER JOIN Common.dbo.Personnel p4 ON t.TestDirectorID =
p4.PersonnelID
> > > LEFT OUTER JOIN Common.dbo.Personnel p5 ON t.EnteredByID =
p5.PersonnelID
> > > LEFT OUTER JOIN Common.dbo.Personnel p6 ON t.SurveyorID =
p6.PersonnelID
> > > WHERE t.statusid = 1
> >
> >
> > I do not see what are all those p2, p3, p4, p5 and p6 sources joined in
for
> > if you don't even select any data from them.
> > This query should give exactly the same results as your one:
> >
> > SELECT DISTINCT
> >   p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName, p.MiddleName,
> >   p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> > FROM TestRecords t
> > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID = p.PersonnelID
> > WHERE t.statusid = 1
> >
> > If in the real query you are using (selcting) the data from the other
> > sources, then post the real query (and the one "using only the fourth
join
> > by itself") - the problem probably lays in those.
> > You may also try to compare those query results without DISTINCT
> > clause - it may help you find the cause.
> >
> >
> > Kamil 'Hilarion' Nowicki
> >
> > PS.: You really should try posting at leas brief description of
"TestRecords"
> >      table structure (and function) with some example data and also
> >      of "Personnel" table (as above: structure, function and example
data)
> >      and results you get with those example data and results you wanted
> >      to get instead.
> >

"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23J10bFf0GHA.4748@TK2MSFTNGP04.phx.gbl...
> You are only selecting the names from one of the tables that you are outer
> joining to, so you will never get the correct results.  You will only get
> the name from PERSONNEL where originatorid = personelid for the first
query
> and where subjectmatterexpert = personnelid in the second query.
>
> Change your select list and this will become more clear.  Run this next
SQL
> just to get a better understanding of what is happening.
>
> SELECT
> p.PersonnelID, p.LastName,
> p2.PersonnelID, p2.LastName,
> p3.PersonnelID, p3.LastName,
> p4.PersonnelID, p4.LastName,
> p5.PersonnelID, p5.LastName,
> t.TestEvalID
> FROM TestEvals t
> LEFT OUTER JOIN Personnel p ON t.OriginatorID = p.PersonnelID
> LEFT OUTER JOIN Personnel p2 ON t.ScreenerID = p2.PersonnelID
> LEFT OUTER JOIN Personnel p3 ON t.SubjectMatterExpertID = p3.PersonnelID
> LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> WHERE t.TestStatus = 1
>
> Now rewrite your query to be simpler, with a single inner join and ORs
>
> SELECT
> p.PersonnelID, p.LastName,
> t.TestEvalID
> FROM TestEvals t
> INNER JOIN Personnel p
> ON t.OriginatorID = p.PersonnelID
>   OR t.ScreenerID = p.PersonnelID
>   OR t.SubjectMatterExpertID = p.PersonnelID
>   OR t.TestDirectorID = p.PersonnelID
>   OR t.EnteredByID = p.PersonnelID
>
> Or, even use an IN clause, although I think the above may perform better,
> you might try both ways.
>
> SELECT
> p.PersonnelID, p.LastName,
> t.TestEvalID
> FROM Personnel p
> INNER JOIN TestEvals t
> ON  p.PersonnelID in
> (
>   t.OriginatorID
>   , t.ScreenerID
>   , t.SubjectMatterExpertID
>   , t.TestDirectorID
>   , t.EnteredByID
> )
>
>
> "Mike Collins" <MikeColl***@discussions.microsoft.com> wrote in message
> news:B85078DE-03C6-43D2-88EC-8357A874E813@microsoft.com...
> > Hopefully this will help. It is too much to create the exact structure
of
> the
> > live database, but this example shows exactly what I need to do.
> >
> > How can I write a query that will return a personnel record for each
> record
> > where that person's id is found in the TestEvals table, regardless which
> > column it is found in? Plus I need it to return distinct rows.
> >
> > The second query below shows a name that does not show up in the first
> > query. I am trying to figure out how to get that name in the second
query
> to
> > show in my first query.
> >
> > Thanks in advance for the help.
> >
> > SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> > FROM TestEvals t
> > LEFT OUTER JOIN Personnel p ON t.OriginatorID = p.PersonnelID
> > LEFT OUTER JOIN Personnel p2 ON t.ScreenerID = p2.PersonnelID
> > LEFT OUTER JOIN Personnel p3 ON t.SubjectMatterExpertID = p3.PersonnelID
> > LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> > LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> > WHERE t.TestStatus = 1
> >
> >
> > SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> > FROM TestEvals t
> > LEFT OUTER JOIN Personnel p ON t.SubjectMatterExpertID = p.PersonnelID
> > LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> > LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> > WHERE t.TestStatus = 1
> >
> > CREATE TABLE [Personnel] (
> > [PersonnelID]  INT NOT NULL ,
> > [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [TestEvals] (
> > [TestEvalID]  INT NOT NULL ,
> > [OriginatorID] [INT] NOT NULL,
> > [ScreenerID] [INT] NOT NULL,
> > [SubjectMatterExpertID] [INT] NOT NULL,
> > [TestDirectorID] [INT] NOT NULL,
> > [EnteredByID] [INT] NOT NULL,
> > [Comments] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> > [TestStatus] [INT] NOT NULL,
> > ) ON [PRIMARY]
> > GO
> >
> >
> > INSERT INTO [TestEvals]
> >
>

> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','3','2','1', '4', '5', 'Comments13', '1')
> >
> >
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('1','Smith')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('2','Johnson')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('3','Collette')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('4','Tyson')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('5','Raymond')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('6','Anderson')
> >
> > "Hilarion" wrote:
> >
> > > Mike Collins wrote:
> > > > Using the following query, I get back three records. I should be
> getting back
> > > > four records. If I run the query using only the fourth join by
itself,
> I will
> > > > see the record that is missing. How can I rewrite this query to get
> back the
> > > > four records I know I am supposed to get back? I hope this enough
> because I
> > > > do not have access to create the structure. Thanks for any help.
> > > >
> > > >
> > > > SELECT DISTINCT p.PersonnelID, p.OrganizationID, p.TitleID,
> p.FirstName,
> > > > p.MiddleName,
> > > > p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress,
p.CommercialPhone
> > > > FROM TestRecords t
> > > > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID =
> p.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p2 ON t.ScreenerID =
> p2.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p3 ON t.SubjectMatterExpertID =
> > > > p3.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p4 ON t.TestDirectorID =
> p4.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p5 ON t.EnteredByID =
> p5.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p6 ON t.SurveyorID =
> p6.PersonnelID
> > > > WHERE t.statusid = 1
> > >
> > >
> > > I do not see what are all those p2, p3, p4, p5 and p6 sources joined
in
> for
> > > if you don't even select any data from them.
> > > This query should give exactly the same results as your one:
> > >
> > > SELECT DISTINCT
> > >   p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName,
p.MiddleName,
> > >   p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress,
p.CommercialPhone
> > > FROM TestRecords t
> > > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID =
p.PersonnelID
> > > WHERE t.statusid = 1
> > >
> > > If in the real query you are using (selcting) the data from the other
> > > sources, then post the real query (and the one "using only the fourth
> join
> > > by itself") - the problem probably lays in those.
> > > You may also try to compare those query results without DISTINCT
> > > clause - it may help you find the cause.
> > >
> > >
> > > Kamil 'Hilarion' Nowicki
> > >
> > > PS.: You really should try posting at leas brief description of
> "TestRecords"
> > >      table structure (and function) with some example data and also
> > >      of "Personnel" table (as above: structure, function and example
> data)
> > >      and results you get with those example data and results you
wanted
> > >      to get instead.
> > >
>
>

"Jim Underwood" wrote:

> You are only selecting the names from one of the tables that you are outer
> joining to, so you will never get the correct results.  You will only get
> the name from PERSONNEL where originatorid = personelid for the first query
> and where subjectmatterexpert = personnelid in the second query.
>
> Change your select list and this will become more clear.  Run this next SQL
> just to get a better understanding of what is happening.
>
> SELECT
> p.PersonnelID, p.LastName,
> p2.PersonnelID, p2.LastName,
> p3.PersonnelID, p3.LastName,
> p4.PersonnelID, p4.LastName,
> p5.PersonnelID, p5.LastName,
> t.TestEvalID
> FROM TestEvals t
> LEFT OUTER JOIN Personnel p ON t.OriginatorID = p.PersonnelID
> LEFT OUTER JOIN Personnel p2 ON t.ScreenerID = p2.PersonnelID
> LEFT OUTER JOIN Personnel p3 ON t.SubjectMatterExpertID = p3.PersonnelID
> LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> WHERE t.TestStatus = 1
>
> Now rewrite your query to be simpler, with a single inner join and ORs
>
> SELECT
> p.PersonnelID, p.LastName,
> t.TestEvalID
> FROM TestEvals t
> INNER JOIN Personnel p
> ON t.OriginatorID = p.PersonnelID
>   OR t.ScreenerID = p.PersonnelID
>   OR t.SubjectMatterExpertID = p.PersonnelID
>   OR t.TestDirectorID = p.PersonnelID
>   OR t.EnteredByID = p.PersonnelID
>
> Or, even use an IN clause, although I think the above may perform better,
> you might try both ways.
>
> SELECT
> p.PersonnelID, p.LastName,
> t.TestEvalID
> FROM Personnel p
> INNER JOIN TestEvals t
> ON  p.PersonnelID in
> (
>   t.OriginatorID
>   , t.ScreenerID
>   , t.SubjectMatterExpertID
>   , t.TestDirectorID
>   , t.EnteredByID
> )
>
>
> "Mike Collins" <MikeColl***@discussions.microsoft.com> wrote in message
> news:B85078DE-03C6-43D2-88EC-8357A874E813@microsoft.com...
> > Hopefully this will help. It is too much to create the exact structure of
> the
> > live database, but this example shows exactly what I need to do.
> >
> > How can I write a query that will return a personnel record for each
> record
> > where that person's id is found in the TestEvals table, regardless which
> > column it is found in? Plus I need it to return distinct rows.
> >
> > The second query below shows a name that does not show up in the first
> > query. I am trying to figure out how to get that name in the second query
> to
> > show in my first query.
> >
> > Thanks in advance for the help.
> >
> > SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> > FROM TestEvals t
> > LEFT OUTER JOIN Personnel p ON t.OriginatorID = p.PersonnelID
> > LEFT OUTER JOIN Personnel p2 ON t.ScreenerID = p2.PersonnelID
> > LEFT OUTER JOIN Personnel p3 ON t.SubjectMatterExpertID = p3.PersonnelID
> > LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> > LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> > WHERE t.TestStatus = 1
> >
> >
> > SELECT Distinct p.PersonnelID, p.LastName, t.TestEvalID
> > FROM TestEvals t
> > LEFT OUTER JOIN Personnel p ON t.SubjectMatterExpertID = p.PersonnelID
> > LEFT OUTER JOIN Personnel p4 ON t.TestDirectorID = p4.PersonnelID
> > LEFT OUTER JOIN Personnel p5 ON t.EnteredByID = p5.PersonnelID
> > WHERE t.TestStatus = 1
> >
> > CREATE TABLE [Personnel] (
> > [PersonnelID]  INT NOT NULL ,
> > [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [TestEvals] (
> > [TestEvalID]  INT NOT NULL ,
> > [OriginatorID] [INT] NOT NULL,
> > [ScreenerID] [INT] NOT NULL,
> > [SubjectMatterExpertID] [INT] NOT NULL,
> > [TestDirectorID] [INT] NOT NULL,
> > [EnteredByID] [INT] NOT NULL,
> > [Comments] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [TestStatus] [INT] NOT NULL,
> > ) ON [PRIMARY]
> > GO
> >
> >
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','1','2','4', '1', '5', 'Comments1', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','2','2', '4', '5', 'Comments2', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','3','3', '4', '5', 'Comments3', '2')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','3','2', '4', '5', 'Comments4', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','1','2', '4', '5', 'Comments5', '3')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','1','1', '4', '5', 'Comments6', '2')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','1','1', '4', '5', 'Comments7', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','1','1', '4', '5', 'Comments8', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','3','1','1', '4', '5', 'Comments9', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','2','2','1', '4', '5', 'Comments10', '1')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','3','4','1', '4', '5', 'Comments12', '2')
> > INSERT INTO [TestEvals]
> >
> ([TestEvalID],[OriginatorID],[ScreenerID],[SubjectMatterExpertID],[TestDirec
> torID],[EnteredByID],[Comments],
> > [TestStatus])VALUES('1','3','2','1', '4', '5', 'Comments13', '1')
> >
> >
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('1','Smith')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('2','Johnson')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('3','Collette')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('4','Tyson')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('5','Raymond')
> > INSERT INTO [Personnel] ([PersonnelID],[LastName])VALUES('6','Anderson')
> >
> > "Hilarion" wrote:
> >
> > > Mike Collins wrote:
> > > > Using the following query, I get back three records. I should be
> getting back
> > > > four records. If I run the query using only the fourth join by itself,
> I will
> > > > see the record that is missing. How can I rewrite this query to get
> back the
> > > > four records I know I am supposed to get back? I hope this enough
> because I
> > > > do not have access to create the structure. Thanks for any help.
> > > >
> > > >
> > > > SELECT DISTINCT p.PersonnelID, p.OrganizationID, p.TitleID,
> p.FirstName,
> > > > p.MiddleName,
> > > > p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> > > > FROM TestRecords t
> > > > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID =
> p.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p2 ON t.ScreenerID =
> p2.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p3 ON t.SubjectMatterExpertID =
> > > > p3.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p4 ON t.TestDirectorID =
> p4.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p5 ON t.EnteredByID =
> p5.PersonnelID
> > > > LEFT OUTER JOIN Common.dbo.Personnel p6 ON t.SurveyorID =
> p6.PersonnelID
> > > > WHERE t.statusid = 1
> > >
> > >
> > > I do not see what are all those p2, p3, p4, p5 and p6 sources joined in
> for
> > > if you don't even select any data from them.
> > > This query should give exactly the same results as your one:
> > >
> > > SELECT DISTINCT
> > >   p.PersonnelID, p.OrganizationID, p.TitleID, p.FirstName, p.MiddleName,
> > >   p.LastName, p.OfficeSymbol, p.Notes, p.EmailAddress, p.CommercialPhone
> > > FROM TestRecords t
> > > LEFT OUTER JOIN Common.dbo.Personnel p ON t.OriginatorID = p.PersonnelID
> > > WHERE t.statusid = 1
> > >
> > > If in the real query you are using (selcting) the data from the other
> > > sources, then post the real query (and the one "using only the fourth
> join
> > > by itself") - the problem probably lays in those.
> > > You may also try to compare those query results without DISTINCT
> > > clause - it may help you find the cause.
> > >
> > >
> > > Kamil 'Hilarion' Nowicki
> > >
> > > PS.: You really should try posting at leas brief description of
> "TestRecords"
> > >      table structure (and function) with some example data and also
> > >      of "Personnel" table (as above: structure, function and example
> data)
> > >      and results you get with those example data and results you wanted
> > >      to get instead.
> > >
>
>
>