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
>

Compatibility and Performance

Is there any performance hit when using a lower compatiblity mode? IE 70 instead of 90?

Compatibility level 70 means you are trying to SQL Server 7.0 mode? Is that what you really want to do? You obviously will not be able to use any SQL 2005 features.

Look for sp_dbcmptlevel in BOL and check out all the info.

|||

Yes we want the database to be in 7.0 mode. I understand the options. What I want to know is... is there a performance hit by using different compatiblity modes? Since SQL 2005 'performs' better than SQL 7 or SQL 2000, will we see any of that performance related to queries etc, or will the added overhead of the compatibility negate the performance improvements.

Also, what performance related issues are there to consider when using different modes, if any?

Thanks,

Raymond Laubert

MCDBA, MCITP:Administrator, MCT