9 Replies Latest reply: Feb 22, 2013 1:40 PM by Frank Kulash RSS

    Need help with Joins

    991357
      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
          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
            monty.latiolais
            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
              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
                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
                  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
                    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
                      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
                        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
                          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.