?
Solved

Can I join two tables on foreign key?

Posted on 2010-11-11
10
Medium Priority
?
883 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Accepted Solution

by:
leoahmad earned 1000 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

764 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