Complex SELECT pulling data from three tables

OK, everyone. I have hit a wall while trying to accomplish the
following goal. I have a table (tblDocInfo) that stores information
about documents. Among the information being stored, I have three
fields that store information about attorneys (ReviewingAtty,
DraftingAtty, ContactAtty) who are usually three different people. The
information I am storing in those three fields is of type INT. Those
numbers correspond to the Primary Key in another table (tblAtty) where
the attorney data is stored. For example:
tblDocInfo {
ID int
ReviewingAtty int
DraftingAtty int
ContactAtty int
docDate date
Comments varchar
}
I am trying to execute a query where I can pull the attorney names
along with some other document information, but I am not getting the
results that I want as the three attorneys are normally different and
only one name is coming accross.
For example, if in "tblDocInfo" I have this data (see format above):
23, 2, 4, 6, 23/09/05, Will
24, 1, 5, 3, 11/05/05, Contract
30, 2, 2, 6, 22/09/05, Letter
41, 4, 4, 5, 23/09/05, Warning
and in "tblAtty" I have this data:
1, John Doe
2, Jane Doe
3, Peter Somebody
4, Joe Blow
5, John Smith
6, Michael Cooper
I would like my query to display this:
23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
Thank you.
MarcosSomething like this...
select a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
from tblDocInfo a
join tblAtty a1
on a1.id = a. ReviewingAtty
join tblAtty a2
on a2.id = a. DraftingAtty
join tblAtty a3
on a3.id = a. ContactAtty
group by a.id,at1.Name,at2.Name,at3.Name,a.docDate,a.comments
MJKulangara
http://sqladventures.blogspot.com|||I only see two tables here, and I think there is certainly a better and more
relational design waiting for you to discover it. But for now:
USE tempdb;
GO
CREATE TABLE dbo.Attorneys
(
AttorneyID INT PRIMARY KEY,
FullName VARCHAR(64)
);
GO
CREATE TABLE dbo.DocumentInfo
(
DocumentID INT PRIMARY KEY,
ReviewingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
DraftingAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
ContactAttorney INT NULL FOREIGN KEY REFERENCES
dbo.Attorneys(AttorneyID),
docDate SMALLDATETIME,
Comments VARCHAR(255)
);
GO
SET NOCOUNT ON;
INSERT dbo.Attorneys SELECT 1, 'John Doe';
INSERT dbo.Attorneys SELECT 2, 'Jane Doe';
INSERT dbo.Attorneys SELECT 3, 'Peter Somebody';
INSERT dbo.Attorneys SELECT 4, 'Joe Blow';
INSERT dbo.Attorneys SELECT 5, 'John Smith';
INSERT dbo.Attorneys SELECT 6, 'Michael Cooper';
INSERT dbo.DocumentInfo SELECT 23, 2, 4, 6, '20050923', 'Will';
INSERT dbo.DocumentInfo SELECT 24, 1, 5, 3, '20050511', 'Contract';
INSERT dbo.DocumentInfo SELECT 30, 2, 2, 6, '20050922', 'Letter';
INSERT dbo.DocumentInfo SELECT 41, 4, 4, 5, '20050923', 'Warning';
SELECT d.DocumentID, a1.FullName, a2.FullName, a3.FullName, d.docDate,
d.Comments
FROM dbo.documentInfo d
LEFT OUTER JOIN dbo.Attorneys a1
ON d.ReviewingAttorney = a1.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a2
ON d.DraftingAttorney = a2.AttorneyID
LEFT OUTER JOIN dbo.Attorneys a3
ON d.ContactAttorney = a3.AttorneyID
GO
DROP TABLE dbo.DocumentInfo;
DROP TABLE dbo.Attorneys;
GO
"bodhipooh" <marcos.velez@.ipm.com> wrote in message
news:1139244065.032852.306060@.o13g2000cwo.googlegroups.com...
> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>|||Thank you all.
First of all, I got the problem solved. I was trying to do the JOIN
operation, but had a mistake in the order (syntax will always get
you!!) Your help is greatly appreciated. Second, I apologize for not
posting my entire table, or much more information. I realize that
information is often necessary to make it possible for others to be
able to help me, but I am working within some confidentiality
constraints and had to minimize the amount of information that was to
be posted. Still your replies have been VERY helpful.
Marcos|||This example may give you some idea on how to go about this:
http://milambda.blogspot.com/2005/0...s-as-array.html
For a better solution please post DDL and sample data.
ML
http://milambda.blogspot.com/|||SELECT t1.[ID], t2.[AttyName], t3.[AttyName], t4.[AttyName], t1.docdate,
t1.comments
FROM tblDocInfo t1
INNER JOIN tblAtty t2 ON t1.ReviewingAtty = t2.AttyID
INNER JOIN tblAtty t3 ON t1.DraftingAtty = t2.AttyID
INNER JOIN tblAtty t4 ON t1.ContactAtty = t2.AttyID
This would work as long as you don't allow any NULLs.
"bodhipooh" wrote:

> OK, everyone. I have hit a wall while trying to accomplish the
> following goal. I have a table (tblDocInfo) that stores information
> about documents. Among the information being stored, I have three
> fields that store information about attorneys (ReviewingAtty,
> DraftingAtty, ContactAtty) who are usually three different people. The
> information I am storing in those three fields is of type INT. Those
> numbers correspond to the Primary Key in another table (tblAtty) where
> the attorney data is stored. For example:
> tblDocInfo {
> ID int
> ReviewingAtty int
> DraftingAtty int
> ContactAtty int
> docDate date
> Comments varchar
> }
> I am trying to execute a query where I can pull the attorney names
> along with some other document information, but I am not getting the
> results that I want as the three attorneys are normally different and
> only one name is coming accross.
> For example, if in "tblDocInfo" I have this data (see format above):
> 23, 2, 4, 6, 23/09/05, Will
> 24, 1, 5, 3, 11/05/05, Contract
> 30, 2, 2, 6, 22/09/05, Letter
> 41, 4, 4, 5, 23/09/05, Warning
> and in "tblAtty" I have this data:
> 1, John Doe
> 2, Jane Doe
> 3, Peter Somebody
> 4, Joe Blow
> 5, John Smith
> 6, Michael Cooper
> I would like my query to display this:
> 23, Jane Doe, Joe Blow, Michael Cooper, 23/09/05, Will
> 24, John Doe, John Smith, Peter Somebody, 11/05/05, Contract
> 30, Jane Doe, Jane Doe, Michael Cooper, 22/09/05, Letter
> 41, Joe Blow, Joe Blow, John Smith, 23/09/05, Warning
>
> Thank you.
> Marcos
>