locked
Best Way to Associate Items in a Table to Each Item in the Same Table RRS feed

  • Question

  • User1440631352 posted

    I have a ticket system in an oracle database, these tickets may have a relationship/association with other tickets.  Since I was not the one who had developed the database schema, I need a clever way to be able to link these items together when there relate to each other, some type of associations.  If you need some more information please let me know.

    The application is MVC3, .net4, and oracle 10 using nhibernate for accessing oracle data.

    Any thoughts suggestions, comments, or complaints always welcomed.

    Coy

    Monday, September 21, 2015 3:07 PM

All replies

  • User269602965 posted

    In Oracle (and other relational db) you would use JOIN on primary and foreign relational keys

    SELECT

       a.TABLE1_PRIMARYKEY_COL

       a.TABLE1_DATACOL1

       a.TABLE1_DATACOL2

       b.TABLE2_DATACOL1

       b.TABLE2_DATACOL2

    FROM

       TABLE1 a

    INNER JOIN TABLE2 b ON a.TABLE1_PRIMARYKEY_COL = b.TABLE2_FOREIGNKEY_COL

    /

    In some cases you would use LEFT OUTER JOIN table2 on keys

    http://www.techonthenet.com/oracle/joins.php

    Monday, September 21, 2015 3:55 PM
  • User2053451246 posted

    You have two options.

    To keep everything in one table you need a field that relates the records.  You would populate this field with the primary key of the "parent" ticket it relates to.

    Another option is to create an additional table, to "bridge" the records.  This table would have one field for the primary key of the parent ticket and one field for the primary key of the related ticket.  This would give you more options on relating tickets then the first method.

    Monday, September 21, 2015 4:41 PM
  • User-271186128 posted

    Hi Coy,

    Best Way to Associate Items in a Table to Each Item in the Same Table

    As for this issue, I agree with Lannie. You could use join. Since, you want to associate items in the same table. You could refer to the following code:

    SELECT columns
    FROM table1 as t1
    INNER JOIN table1 as t2
    ON t1.column = t2.column;

    Best regards.
    Dillion

    Wednesday, September 23, 2015 5:26 AM