Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can I join two tables on foreign key?

Posted on 2010-11-11
10
Medium Priority
?
889 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:
Muhammad Ahmad Imran 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:Muhammad Ahmad Imran
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

636 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