Solved

Can I join two tables on foreign key?

Posted on 2010-11-11
10
858 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
Comment Utility
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
Comment Utility
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
Comment Utility
SELECT *
FROM req, osi
WHERE req.orc_key = osi.orc_key ;
0
 
LVL 3

Expert Comment

by:mpaladugu
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:dportas
Comment Utility
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
Comment Utility
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
Comment Utility
thanks, that why u r sage :)
0
 

Author Comment

by:donvike1
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now