Databases
StackOverflow2013
Postsdbo
POGetting hierarchieal data using SQL - Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGetting hierarchieal data using SQL - Oracle
    primarykey
    data
    text
    <p>Following are my tables</p> <pre><code>PARTY ID, PARTY_ID, PARENT_ID PARTY_PARTY BUYER_ID, SUPPLIER_ID </code></pre> <p><code>PARTY</code> table holds records of both Buyer and Supplier. Buyer will have one or more child-buyers(referred by <code>PARENT_ID</code> column by child-buyer) and those child-buyers can have child-buyers in turn.</p> <p>Each buyer can have one or more suppliers as well and this association tracked in <code>PARTY_PARTY</code> table, where <code>BUYER_ID</code> column holds buyer id and <code>SUPPLIER_ID</code> column holds his supplier id.</p> <p>I am able to get all buyers and suppliers of given buyer like this</p> <p><strong>Getting all hierarchical buyers for given buyer</strong></p> <pre><code>SELECT ID FROM party START WITH party_id = 'BUYERX' CONNECT BY PRIOR ID = parent_id; </code></pre> <p><strong>Getting all suppliers of above buyers</strong></p> <pre><code>SELECT ID FROM party WHERE ID IN ( SELECT SUPPLIER_ID FROM party_party WHERE BUYER_ID IN ( SELECT ID FROM party START WITH party_id = 'BUYERX' CONNECT BY PRIOR ID = parent_id ) ) </code></pre> <p>But the problem here is - that resultant supplier can act as buyer for another supplier. In that case how can I get all buyers and suppliers, where buyers include both buyers and suppliers acting as buyers.</p> <p>I don't want to use any views or procedures!</p> <p><strong>Sample Data</strong></p> <pre><code>PARTY ID, PARTY_ID, PARENT_ID 1, BX, NULL 2, BY, 1 3, BZ, 1 4, SA, NULL 5, SB, NULL 6, SC, NULL 7, SD, NULL 8, SE, NULL 9, SF, NULL PARTY_PARTY BUYER_ID, SUPPLIER_ID 1, 4 2, 5 3, 6 6, 7 6, 8 8, 9 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPokuri
    1. USPokuri
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COTake a look at my edited answer to check if the output of my query for your sample data is what you expect.
      singulars
      1. POGetting hierarchieal data using SQL - Oracle
      1. USPrzemyslaw Kruglej
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload