How to Join two tables thrice?
Author |
Message |
Andrew Cart #1 / 5
|
 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 |
|
 |
David W. Fores #2 / 5
|
 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 |
|
 |
Ronald In 't Velt #3 / 5
|
 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 |
|
 |
Mark Harpe #4 / 5
|
 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 |
|
 |
Larry Tubb #5 / 5
|
 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 |
|
|
|