I have two tables: Names (uid, Name) and Log (WriterUid, ReviewerUid,
DocumentNum). The Log table contains two User Id's that link back to
the Names table.

How can I, in one SQL statement, return one row that contains the
Names corresponding to the User Id's in Log?  I want the resulting
table to look something like this:

Result( WriteUid, WriterName, ReviewerUid, ReviewerName, DocumentNum)

/*

Computing Analyst
Oak Ridge National Laboratory
http://www.*-*-*.com/
*/

SELECT log.writeruid, Names.name, log.revieweruid, Names_1.name, log.docnum
FROM (log INNER JOIN Names ON log.writeruid = Names.uid) INNER JOIN Names
AS Names_1 ON log.revieweruid = Names_1.uid;

--
David W. Forest
LAN Specialist
Information Technologies
Lear Corporation - Ford Division
SBN Level 2

If you're not scared, you don't understand.

SELECT DISTINCTROW Log.WriterUid, Names.Name, Names_1.Name, Log.DocNum
FROM (Log INNER JOIN Names ON Log.WriterUid=Names.uid) INNER JOIN
Names AS Names_1 ON Log.ReviewerUid=Names_1.uid;

I hope I got the syntax right.  Anyway this should give you an idea
on how to do the double-jointed stuff.

Ronald

Use a self-join

eg.
SELECT Writer.Uid,  Writer.Name, Reviewer.Uid, Reviewer.Name ,
Log.DocumentNum
FROM Names Writer, Names Reviewer, Log
WHERE Writer.Uid = Log.WriterUid
AND Reviewer.Uid = Log.ReviewerUid

*poof* magic

The secret is that I listed Names twice in the FROM clause using different
aliases.
This causes the same table to be treated as two different tables

Hello Andrew,

Try This:
strSQL = "SELECT DISTINCTROW Log.WiterID, Names.Name, Names_1.UID,
Names_1.Name
FROM (Log INNER JOIN Names ON Log.WiterID = Names.UID) INNER JOIN Names AS
Names_1 ON Log.ReviewID = Names_1.UID;"

Where you have two tables, one called "Names" with fields "UID" and "Name",
and another called "Log" with fields (misspelled, I know) "WiterID" and
"ReviewID".

I hope this helps,

Larry Tubbs, MCPS, MCSD
Silverleaf Vacation Club

http://www.flash.net/~enigma/