This discussion is archived
9 Replies Latest reply: Feb 22, 2013 11:40 AM by Frank Kulash RSS

Need help with Joins

991357 Newbie
Currently Being Moderated
Hi,
Below are the tables that i want to join and the query. Can you please tell me where my join is wrong.
Desc Trial
Name                      Null     Type              
------------------------- -------- ----------------- 
TRIAL_ID                  NOT NULL NUMBER(20)        
MIDB_ID                            VARCHAR2(15 CHAR) 
DATA_SOURCE_ID                     VARCHAR2(15 CHAR) 
DATA_SOURCE                        VARCHAR2(10 CHAR) 


DESC Organization
Name              Null     Type                
----------------- -------- ------------------- 
ORGANIZATION_ID   NOT NULL NUMBER(20)          
MIDB_ID                    VARCHAR2(15 CHAR)   
DATA_SOURCE_ID             VARCHAR2(15 CHAR)   
DATA_SOURCE                VARCHAR2(10 CHAR)   


DESC CONTACT
Name                 Null     Type                
-------------------- -------- ------------------- 
CONTACT_ID           NOT NULL NUMBER(20)          
MIDB_ID                       VARCHAR2(15 CHAR)   
DATA_SOURCE_ID                VARCHAR2(15 CHAR)   
DATA_SOURCE                   VARCHAR2(10 CHAR) 


DESC  SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S1
Name                      Null     Type                
------------------------- -------- ------------------- 
ROW_ID                    NOT NULL VARCHAR2(15 CHAR)   
CL_PTCL_ID                NOT NULL VARCHAR2(15 CHAR)   
PR_INVES_CON_ID           NOT NULL VARCHAR2(15 CHAR)   
SITE_ORG_ID               NOT NULL VARCHAR2(15 CHAR)   


Query:

SELECT O.MIDB_ID, T.MIDB_ID, C.MIDB_ID
FROM ORGANIZATION O,
            TRIAL T,
            CONTACT C,
            SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S1,
            SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S2,
            SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S3,
          WHERE S1.SITE_ORG_ID(+)=O.MIDB_ID
          AND S2.CL_PTCL_ID(+)=T.MIDB_ID
          AND S3.PR_INVES_CON_ID(+)=C.MIDB_ID
Can any one guide me how to do the join in between these 4 tables. I am thinking to have a cursor but i dont know how to do it. Please help me.

Thanks

Sami

Edited by: 988354 on Feb 22, 2013 9:34 AM
  • 1. Re: Need help with Joins
    sb92075 Guru
    Currently Being Moderated
    988354 wrote:
    Hi,
    Below are the tables that i want to join and the query. Can you please tell me where my join is wrong.
    we don't know what question you are trying to answer.

    With 7 tables in the FROM clause, at least 6 joins are required in the WHERE clause to avoid Cartesian Product
  • 2. Re: Need help with Joins
    879555 Newbie
    Currently Being Moderated
    Sami,

    Unless you desire some sort of cartesian, you should specifiy a join condition between organization, trial and and contact.

    Monty
  • 3. Re: Need help with Joins
    991357 Newbie
    Currently Being Moderated
    I have only 4 tables trial, Organization, contact and SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS

    Relation between tables is
    S1.SITE_ORG_ID=O.MIDB_ID
    S1.CL_PTCL_ID=T.MIDB_ID
    S1.PR_INVES_CON_ID=C.MIDB_ID

    I dont wnat a Cartesian Product, can you tell me how i need to join these tables.

    Thanks
    Sami
  • 4. Re: Need help with Joins
    991357 Newbie
    Currently Being Moderated
    There is no relation between trial, Organization and Contact...
    these 3 tables are related though SIEBEL.S_CL_SUBJ_LS@MIDB.PH.CHBS

    Thanks
    Sami
  • 5. Re: Need help with Joins
    sb92075 Guru
    Currently Being Moderated
    988354 wrote:
    I have only 4 tables trial, Organization, contact and SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS

    Relation between tables is
    S1.SITE_ORG_ID=O.MIDB_ID
    S1.CL_PTCL_ID=T.MIDB_ID
    S1.PR_INVES_CON_ID=C.MIDB_ID

    I dont wnat a Cartesian Product, can you tell me how i need to join these tables.

    Thanks
    Sami
    I count SEVEN lines below
    FROM ORGANIZATION O,
                TRIAL T,
                CONTACT C,
                SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S1,
                SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S2,
                SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S3,
                SIEBEL.S_CL_SUBJ_LS@MIDB.PH.CHBS NS
    It does matter that SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS occurs 3 times!
  • 6. Too Little Information
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Sami,
    988354 wrote:
    Hi,
    Below are the tables that i want to join and the query. Can you please tell me where my join is wrong.
    No, not without more information.

    There is no single right way to join any set of tables. You might join the same tables different ways in different queries, depending on what data is in the tables and what results you want from that data. All of the different joins could be right for a different purpose. Unless you post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data, no one can tell why the query you posted doesn't produce those results, or even if it doesn't produce the right results.
    Query:
    
    SELECT O.MIDB_ID, T.MIDB_ID, C.MIDB_ID
    FROM ORGANIZATION O,
    TRIAL T,
    CONTACT C,
    SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S1,
    SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S2,
    SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS S3,
    SIEBEL.S_CL_SUBJ_LS@MIDB.PH.CHBS NS
    WHERE S1.SITE_ORG_ID(+)=O.MIDB_ID
    AND S2.CL_PTCL_ID(+)=T.MIDB_ID
    AND S3.PR_INVES_CON_ID(+)=C.MIDB_ID
    It's suspicious that table ns is not mentioned in any of the join conditions. It's even more suspicious that table ns isn't used at all. That doesn't mean it's wrong.
    Also, it's unusual that there is no connection, either direct or indirect, between tables o, t and c. Again, that's not necessarily wrong, depending on your data and what results you want.
    Can any one guide me how to do the join in between these 4 tables.
    It's more productive to think of this a joining 7 tables, where 4 of them (s1, s2, s3 and ns) happen to be identical.

    Perhaps you don't mean to join 4 different copies of the same table. Perhaps you mean something like this:
    SELECT  O.MIDB_ID, T.MIDB_ID, C.MIDB_ID
    FROM      ORGANIZATION                  O,
            TRIAL                        T,
            CONTACT                   C,
            SIEBEL.S_CL_SUBJ_LS@MIDB.PH.CHBS   NS
    WHERE      NS.SITE_ORG_ID          = O.MIDB_ID (+)
    AND     NS.CL_PTCL_ID          = T.MIDB_ID (+)
    AND      NS.PR_INVES_CON_ID     = C.MIDB_ID (+)
    ;
    The query above is one correct way to get one type of results, but, again, there are literally hundreds of different results that you might want, each needing a different query.
  • 7. Re: Too Little Information
    991357 Newbie
    Currently Being Moderated
    SELECT  O.MIDB_ID, T.MIDB_ID, C.MIDB_ID
    FROM      ORGANIZATION                  O,
            TRIAL                        T,
            CONTACT                   C,
            SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS  S
    WHERE      S.SITE_ORG_ID= O.MIDB_ID (+)
    AND     S.CL_PTCL_ID= T.MIDB_ID (+)
    AND      S.PR_INVES_CON_ID = C.MIDB_ID(+)
    ;
    when i do join like this i dont get any data. for some reason all columns are null....
    I want to join only these 4 tables
    ORGANIZATION, TRIAL      , CONTACT , SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS

    using following relation ship

    S.SITE_ORG_ID= O.MIDB_ID
    S.CL_PTCL_ID= T.MIDB_ID
    S.PR_INVES_CON_ID = C.MIDB_ID

    And i dont want cartisen product.

    for one row in SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS i should get out put one row

    Thanks
    Sami
  • 8. Re: Too Little Information
    Christine Schnittker Explorer
    Currently Being Moderated
    Hi Sami,

    what you describe is exactly what Frank's select is doing - only the select is showing the content of the (outer) joined tables rather than the data from SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS

    How many rows of "all null columns" are you getting? This suspiciously sounds like the listed join conditions are wrong.


    //Tine
  • 9. Re: Too Little Information
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Sami,
    988354 wrote:
    when i do join like this i dont get any data. for some reason all columns are null....
    The data in the tables might not be what you expect.
    Unless you post some data, nobody can re-create the problem, and that makes it very unlikely that anybody can help you. We need your help to solve this problem. Post some sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
    I want to join only these 4 tables
    ORGANIZATION, TRIAL      , CONTACT , SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS

    using following relation ship

    S.SITE_ORG_ID= O.MIDB_ID
    S.CL_PTCL_ID= T.MIDB_ID
    S.PR_INVES_CON_ID = C.MIDB_ID
    Are you saying that you want to use those join conditions, regardless of what output it produces? Why? Don't you care about the output? You were complaining earlier that the output was all NULL.
    And i dont want cartisen product.
    What do you want? Post a little sample data (CREATE TABLE and INSERT statements) for all tables, and the results you want from that sample data. You don't have to post a whole lot of data; you can probably show what you need to do with somewhere between 2 and 5 rows in each table.
    for one row in SIEBEL.S_PTCL_SITE_LS@MIDB.PH.CHBS i should get out put one row
    The query I posted is guaranteed to produce at least 1 row of output for each row in that table.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points