1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Joins in reports

Discussion in 'Oracle Forms and Reports' started by prashantrais, May 7, 2009.

  1. prashantrais

    prashantrais Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    75
    Hi Gurus,

    Im new to oracle reporting, and i would like to know whats the logic behind joining tables, how to do it .

    Suppose im using 5 tables from AP and GL how to find out the exact joins.

    pls do help

    thanks

    prashantrais
     
  2. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    To join the tables you must have use the primary keys and foreign keys

    view the tables structure of your tables that you wish to join the primary key of the table one is reffed to the foreign in the 2nd table

    this is the simple way of joining.............
     
  3. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    yes exactly as Salman said earlier. For example in AP you have the ap_invoices_all table which has a unique invoice_id. This invoice_id would be the foreign key in many AP, GL tables stored under invoice_id column or maybe another name. So to join you have to use "equal to" condition for the invoice id between these two tables.

    You should refer to Technical reference manual if you want to study more about the tables. The following is the link to download all TRMs

    http://download.oracle.com/docs/cd/A88418_01/html/trmdoc.html
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am just wondering , why do we need primary key and foreign keys to join two tables in Oracle Developer suite. Isn't it possible to join 2 tables not having primary and foreign keys ?
     
  5. salmankhalid

    salmankhalid Forum Advisor

    Messages:
    116
    Likes Received:
    6
    Trophy Points:
    260
    Location:
    Lahore, Pakistan
    No its actually not necessary to join the table on the primary key or foreign key, its a simple concept you can also join the table on unique key, or the tables that don't have primary or foreign key can also be joined.............
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Ok .. If that is the case , I recommend OP to refer the following link on SQL JOIN

    SQL JOIN
     
  7. djbabu

    djbabu Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Join just needs a 'similiar value' on the other table's column which is the be joinned..


    Excellent link Rajavu !

    have a question for you..
    is there a difference between key words :

    LEFT OUTER JOIN vs LEFT JOIN
    and
    RIGHT OUTER JOIN vs RIGHT JOIN

    so do we really need to code the key word "OUTER" ?!?

    sorry for asking this Q in a different subject ...
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No difference at all ... .. .

    Code (SQL):

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2    FROM   scott.EMP_MGR E LEFT OUTER JOIN scott.DEPT_MAST D
      3    ON     E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7934         10 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7902         20 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7369         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          2000         50

    15 ROWS selected.

    SQL> SELECT E.EMPNO , E.DEPTNO,D.DNAME , D.LOC
      2    FROM   scott.EMP_MGR E LEFT JOIN scott.DEPT_MAST D
      3    ON     E.DEPTNO = D.DEPTNO;

         EMPNO     DEPTNO DNAME          LOC
    ---------- ---------- -------------- -------------
          7934         10 ACCOUNTING     NEW YORK
          7839         10 ACCOUNTING     NEW YORK
          7782         10 ACCOUNTING     NEW YORK
          7902         20 RESEARCH       DALLAS
          7876         20 RESEARCH       DALLAS
          7788         20 RESEARCH       DALLAS
          7566         20 RESEARCH       DALLAS
          7369         20 RESEARCH       DALLAS
          7900         30 SALES          CHICAGO
          7844         30 SALES          CHICAGO
          7698         30 SALES          CHICAGO
          7654         30 SALES          CHICAGO
          7521         30 SALES          CHICAGO
          7499         30 SALES          CHICAGO
          2000         50

    15 ROWS selected.

    SQL>

     
     
  9. djbabu

    djbabu Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks raj,

    I too tested the same, even the timings are same, so we can use as given below which is clear...

    INNER JOIN
    LEFT JOIN
    RIGHT JOIN
    CROSS JOIN

    thx.