Can I join two tables on foreign key?

try to understand via scott schema

SQL> select e.ename,d.dname from emp e,dept d where e.deptno(+) = d.deptno
  2  /

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
SMITH      RESEARCH
ADAMS      RESEARCH
FORD       RESEARCH
SCOTT      RESEARCH
JONES      RESEARCH
ALLEN      SALES
BLAKE      SALES
MARTIN     SALES
JAMES      SALES
TURNER     SALES
WARD       SALES
           OPERATIONS

15 rows selected.

SQL> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

SQL> 

Select allOpen in new window

SELECT *
FROM req, osi
WHERE req.orc_key = osi.orc_key ;

you will not get any syntax error or runtime error, if you join tables using foreign keys on both sides,
but you may not get the results you intended.
In your scenario ORC_KEY is FK on both tables which means both are referring to some third table and joining these tables REQ and OSI on FK is not going to give you logically correct results(unless you have some ad-hoc requirement)

to be logically as per RDBMS concepts, the query given by leoahmad is the right one, other wise use dportas query.

Since REQ_KEY column of OSI table has no values populated in it, your query may return Zero rows, to avoivd that you can use a outer join,  in that case use my query

select r.req_key,r.orc_key,o.sch_key,o.orc_key
from req r, osi o
where r.req_key = o.req_key(+);

Further to mpaladugu's comment. My query is of course a guess based on the little information given here. I don't know the structure of your tables or which column references which so please test it out yourself.

I don't know your requirements either so I can't tell whether or not my query does or doesn't give the result you want. There is nothing in "RDBMS concepts" to say you can't join on ANY attributes(s) you like, whether foreign key or not.

mpaladugu, Good suggestion on using an outer join. But the legacy (+) syntax has been deprecated for years. I recommend using the standard OUTER JOIN syntax instead. It is more powerful and is understood by more people.

SELECT *
FROM req, osi
WHERE req.orc_key = osi.orc_key ;

I have the above as a full outer join, see my code at the bottom.  All of the joins works (meaning I am able to extract data) prior to adding OSI (f), and the code will add the OSI fields but when I go to extract data from the OSI the rows come back empty.  What am I doing wrong in joining the foreign key?

CREATE TABLE OIM_REPORTING.TEST_REQUEST_DETAILS AS

    (SELECT a.REQ_KEY,
    a.OST_KEY,
    a.ORC_KEY,
    a.USR_KEY,
    REQ_TYPE,
    REQ_TARGET_TYPE,
    REQ_PRIORITY,
    REQ_OBJ_ACTION,
    REQ_STAGE_FLAG,
    REQ_NUMBER,
    REQ_SCHED_PROV,
    REQ_PROV_SCHED_DATE,
    REQ_PROV_DATE,
    REQ_DATA_LEVEL,
    REQ_CREATE,
    REQ_CREATEBY,
    REQ_UPDATE,
    REQ_UPDATEBY,
    REQ_NOTE,
    REQ_ROWVER,
    REQ_CONSOLIDATED_DATA_VALUE,
    c.ACT_KEY,
    USR_LAST_NAME,
    USR_FIRST_NAME,
    USR_MIDDLE_NAME,
    USR_MANAGER,
    USR_TYPE,
    USR_LOCATION,
    USR_FSS,
    USR_TODO,
    USR_PASSWORD,
    USR_DISABLED,
    USR_PWD_CANT_CHANGE,
    USR_PWD_MUST_CHANGE,
    USR_PWD_NEVER_EXPIRES,
    USR_UPDATE_AD,
    USR_CREATED,
    USR_STATUS,
    USR_EMP_TYPE,
    USR_LOGIN,
    USR_DISABLED_BY_PARENT,
    USR_PWD_EXPIRE_DATE,
    USR_PWD_WARN_DATE,
    USR_MANAGER_KEY,
    USR_POLICY_UPDATE,
    USR_PWD_WARNED,
    USR_PWD_EXPIRED,
    USR_START_DATE,
    USR_END_DATE,
    USR_PROVISIONING_DATE,
    USR_DEPROVISIONING_DATE,
    USR_PROVISIONED_DATE,
    USR_DEPROVISIONED_DATE,
    USR_EMAIL,
    USR_LOCKED,
    USR_LOGIN_ATTEMPTS_CTR,
    USR_PWD_RESET_ATTEMPTS_CTR,
    USR_CHANGE_PWD_AT_NEXT_LOGON,
    USR_PWD_MIN_AGE_DATE,
    USR_DATA_LEVEL,
    USR_CREATE,
    USR_CREATEBY,
    USR_UPDATE,
    USR_UPDATEBY,
    USR_NOTE,
    USR_ROWVER,
    USR_UDF_NICKNAME,
    USR_UDF_COUNTRY,
    USR_UDF_HIERARCHY_CODE,
    USR_UDF_PERSON_NUMBER,
    USR_UDF_FUNCTIONAL_TITLE,
    USR_UDF_TELEPHONE,
    USR_UDF_SSN,
    USR_UDF_OFFICE_NAME,
    USR_UDF_STREET_ADD,
    USR_UDF_MAIL_CODE,
    USR_UDF_CITY,
    USR_UDF_STATE,
    USR_UDF_HIRE_DATE,
    USR_UDF_ZIP_CODE,
    USR_UDF_CLIENT_STATUS,
    USR_UDF_HR_JOB_CODE,
    USR_UDF_COMPANY_DESCRIPTION,
    USR_UDF_HIERARCHY_DESCRIPTION,
    USR_UDF_USER_BAND,
    USR_UDF_USER_INITIALS,
    USR_UDF_PH_HOME,
    USR_UDF_MGR_PERSON_NUMBER,
    USR_UDF_MEMO,
    USR_UDF_LOB_CODE,
    USR_UDF_LOB_NAME,
    USR_UDF_SUB_LOB_CODE,
    USR_UDF_SUB_LOB_NAME,
    USR_UDF_DIVISION_CODE,
    USR_UDF_DIVISION_NAME,
    USR_UDF_SUB_DIVISION_CODE,
    USR_UDF_SUB_DIVISION_NAME,
    USR_UDF_GROUP_CODE,
    USR_UDF_GROUP_NAME,
    USR_UDF_RETURN_TO_WORK_DATE,
    USR_UDF_FLAG,
    USR_UDF_COMPANY_NUMBER,
    USR_UDF_COST_CENTER,
    RQU_DATA_LEVEL,
    RQU_CREATE,
    RQU_CREATEBY,
    RQU_UPDATE,
    RQU_UPDATEBY,
    RQU_NOTE,
    RQU_ROWVER,
    OBJ_KEY,
    OST_STATUS,
    OST_LAUNCH_DEPENDENT,
    OST_REMOVED,
    OST_DATA_LEVEL,
    OST_CREATE,
    OST_CREATEBY,
    OST_UPDATE,
    OST_UPDATEBY,
    OST_NOTE,
    OST_ROWVER,
    SDK_KEY,
    OBJ_ORDER_FOR,
    OBJ_TYPE,
    OBJ_ALLOW_MULTIPLE,
    OBJ_SELF_REQUEST_ALLOWED,
    OBJ_NAME,
    OBJ_AUTOSAVE,
    OBJ_ALLOWALL,
    OBJ_AUTOLAUNCH,
    OBJ_OBJADMINONLY,
    OBJ_AUTO_PREPOP,
    OBJ_TRUSTED_SOURCE,
    OBJ_INITIAL_RECON_DATE,
    OBJ_DATA_LEVEL,
    OBJ_CREATE,
    OBJ_CREATEBY,
    OBJ_UPDATE,
    OBJ_UPDATEBY,
    OBJ_NOTE CLOB,
    OBJ_ROWVER,
    OBJ_RECON_SEQUENCE,
    OBJ_OFFLINED,
    OBJ_UDF_RESOBJ_TYPE,
    SCH_KEY,
    MIL_KEY,
    TLG_KEY,
    RSC_KEY,
    OSI_RECOVERY_FOR,
    OSI_RETRY_FOR,
    OSI_ASSIGNED_TO,
    TOS_KEY,
    PKG_KEY,
    ORD_KEY,
    ORC_SUPPCODE,
    OSI_ASSIGN_TYPE,
    OSI_ESCALATE_ON,
    OSI_ASSIGNED_TO_USR_KEY,
    OSI_ASSIGNED_TO_UGP_KEY,
    OSI_RETRY_ON,
    OSI_RETRY_COUNTER,
    OSI_CHILD_TABLE_KEY,
    OSI_CHILD_OLD_VALUE,
    OSI_ASSIGNED_DATE,
    SCH_INT_KEY,
    OSI_LOG_KEY,
    OSI_DATA_LEVEL,
    OSI_CREATE,
    OSI_CREATEBY,
    OSI_UPDATE,
    OSI_UPDATEBY,
    OSI_NOTE,
    OSI_ROWVER
from MV_REQ_PK a
full outer join MV_RQU_PK b on a.REQ_KEY = b.REQ_KEY
full outer join MV_USR_PK c on a.USR_KEY = c.USR_KEY
full outer join MV_OST_PK d on a.OST_KEY = d.OST_KEY
full outer join MV_OBJ_PK e on d.OBJ_KEY = e.OBJ_KEY
full outer join MV_OSI_PK f on a.ORC_KEY = f.ORC_KEY);

thanks, that why u r sage :)

select r.req_key,r.orc_key,o.sch_key,o.orc_key
from req r, osi o
where r.req_key = o.req_key;

My req_key column is empty in osi, that is why I'm trying to use the orc_key to join on in req and osi.
TABLE REQ            TABLE OSI
 REQ_KEY PK          SCH_KEY PK
 ORC_KEY FK           REQ_KEY FK (but this field is empty in this table)
                                 ORC_KEY FK (this field is populated)

I they join, but I'm not getting data in the fields only nulls.  I know the data is there.

It looks like you have the wrong column in your query. To join in orc_key use:

select r.req_key,r.orc_key,o.sch_key,o.orc_key
from req r, osi o
where r.req_key = o.orc_key;

or maybe:

select r.req_key,r.orc_key,o.sch_key,o.orc_key
from req r, osi o
where r.orc_key = o.orc_key;