How to Join the same named columns of different tables

Here I describe开发者_开发知识库 my issue more simply about what kind of query I need:

Consider that I have three tables AccessPolicy, Document and Term. Here both the names of the AccessPolicy and the Name of the Document is stored in the “Term” table. I need to return the following fields from the 3 tables. I need to fetch the Name field two time for AccessPolicy and the Document respectively in a single select query.

Term.Name as AccessPolicyName
Term.Name as DocumentName
AccessPolicy.TermID
Document.TermID
Document.UserName
AccessPolicy.ValidFrom

How to form a select query to return the above result?

I need to do this in Oracle 11g.

select
    Term.Name as AccessPolicyName,
    Term.Name as DocumentName,
    AccessPolicy.TermID,
    Document.TermID,
    Document.UserName,
    AccessPolicy.ValidFrom
from Term t
left join AccessPolicy ap on ap.Name = t.Name
left join Document d on d.Name = t.Name;

Note that both AccessPolicy and Document info may be null

You haven't said how the tables are related. Presumably the TermID in the AccessPolicy and Document tables relate to an entry in the Term table, perhaps identified by a column called ID. But we don't know if you're expecting the same TermID in both - so there's a single Term record that links the other two tables. Since you're pulling the name twice I'd have thought not, but then there's no indication of how the join should be made.

If there are two TermID values involved then you need to join to Term twice, something like:

select t1.Name as AccessPolicyName,
    t2.Name as DocumentName,
    ap.TermID as AccessPolicyTermID,
    d.TermID as DocumentTermID,
    d.UserName,
    ap.ValidFrom
from AccessPolicy ap
join Term t1 on t1.ID = ap.TermID
join Document d on d.someColumn = ap.someColumn -- don't know how policy and document are related?
join Term t2 on t2.ID = d.TermID