How to Join two tables thrice? 
Author Message
 How to Join two tables thrice?

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/
*/



Sat, 03 Jul 1999 03:00:00 GMT  
 How to Join two tables thrice?

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.



Quote:
> 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.ornl.gov
> */



Sat, 03 Jul 1999 03:00:00 GMT  
 How to Join two tables thrice?

Quote:

> 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)

Try:

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



Sat, 03 Jul 1999 03:00:00 GMT  
 How to Join two tables thrice?

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



Quote:
> 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.ornl.gov
> */



Thu, 08 Jul 1999 03:00:00 GMT  
 How to Join two tables thrice?

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/



Quote:
> 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.ornl.gov
> */



Sat, 10 Jul 1999 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Joining two tables from two databases

2. Here's a sticky wicket - joining two tables on two diffrent servers with ADO

3. Table join between two tables in different databases

4. Joining two tables from different databases

5. Creating a join on tables in two different MSSQL 6.5 DB's

6. How to Left join more than two table ?

7. SQL: Non-Equal Join of Two Tables

8. SQL: Non-Equal Join of Two Tables

9. Join two tables

10. Joining two tables on different length fields

11. Join two tables from seperate databases

12. Table join between two different databases (MSSQL 6.5)

 

 
Powered by phpBB® Forum Software