Solved

Can I join two tables on foreign key?

Posted on 2010-11-11
10
871 Views
Last Modified: 2012-05-10
Can I join two tables on the foreign key?  See my table below.

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)

Thanks
0
Comment
Question by:donvike1
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Accepted Solution

by:
leoahmad earned 250 total points
ID: 34111397
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;

0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34111424
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> 

Open in new window

0
 
LVL 22

Expert Comment

by:dportas
ID: 34111433
SELECT *
FROM req, osi
WHERE req.orc_key = osi.orc_key ;
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Expert Comment

by:mpaladugu
ID: 34111948
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(+);

0
 
LVL 22

Expert Comment

by:dportas
ID: 34112099
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 34112113
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.
0
 

Author Comment

by:donvike1
ID: 34112235
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);


0
 
LVL 3

Expert Comment

by:mpaladugu
ID: 34112262
thanks, that why u r sage :)
0
 

Author Comment

by:donvike1
ID: 34112595
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.
0
 
LVL 22

Expert Comment

by:dportas
ID: 34379191
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;
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question