logo
down
shadow

Hierarchical Query / base on table input


Hierarchical Query / base on table input

By : Adnan Khalil
Date : November 22 2020, 09:00 AM
Hope that helps An approach may be to first union the missing hierarchy from product and info into account and then generate the results... Since Product and info have relevant data to the hierarchy we need to combine them. I think this is likely the part that was the most troubling. Not all the hierarchy data was in one place. Once I realized that, the need for a union to allow the connect by prior to work was born.
So we join product to info to get the needed additional hierarchical data and union this to the account table. This results in a dataset containing all the needed parent child relationships.
code :
SELECT a.id_acc, level as lvl
FROM (SELECT id_acc, parent_acc FROM account 
      UNION 
      SELECT P.Account_ID, i.account_id  FROM info i
      INNER JOIN product p 
       on I.Product_ID=P.Product_ID) A
START WITH A.ID_ACC=76543
CONNECT BY prior  a.id_acc=a.parent_acc
Account Lvl
76543   1
18252   2
456452  3
34456   2
456567  2
SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , CONNECT_BY_ISLEAF "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      INNER JOIN product p 
        on I.Product_ID=P.Product_ID) A
START WITH a.id_acc=76543
CONNECT BY nocycle prior a.id_acc=a.parent_acc
ID_acc  lvl Path                  isLeaf
76543   1   /76543                0
18252   2   /76543/18252          0
456452  3   /76543/18252/456452   1
34456   2   /76543/34456          1
456567  2   /76543/456567         1
select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      inner join product p 
        on i.product_id=p.product_id) a
where parent_acc is null
connect by  prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show)
SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , connect_by_isleaf "IsLeaf"
FROM (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      INNER JOIN product p 
        on i.product_id=p.product_id) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in 
(
select distinct ID_ACC
from (SELECT id_acc, parent_acc FROM account 
      UNION
      SELECT P.Account_ID, i.account_id  
      FROM info i
      inner join product p 
        on i.product_id=p.product_id) a
where parent_acc is null
connect by  prior a.parent_acc = a.id_acc
start with id_acc in (select id from to_show))
with 
--Let's combine the hierarchies so they are all in one place.
hsource as (
   select id_acc, parent_acc from account 
   UNION
   SELECT P.Account_ID, i.account_id  
   FROM info i
   inner join product p 
   on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
   select distinct id_acc  
   from (hSource) a
   where parent_acc is null  --this null compare is actually what tells us it's a root/Top domain!  Assumption being all roots have null parent_Acc!
   connect by  prior a.parent_acc = a.id_acc
   start with id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
SELECT a.id_acc
     , level as lvl
     , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
     , connect_by_isleaf "IsLeaf"
FROM (Select id_acc, parent_acc from hSource) a
connect by nocycle prior a.id_acc=a.parent_acc
start with id_acc in  (Select ID_ACC from Top_Domain)
with 
--Let's combine the hierarchies so they are all in one place.
hsource as (
   select id_acc, parent_acc from account 
   UNION
   SELECT P.Account_ID, i.account_id  
   FROM info i
   inner join product p 
   on i.product_id=p.product_id),
--Now lets get the top domain or root nodes of each item in To_Show
top_domain as (
   select distinct a.id_acc
   from (hsource) a
--Modification starts here...
   where case when  a.parent_acc =a.id_Acc then null else a.parent_Acc end is null
   connect by prior (case when a.parent_acc=a.id_acc then null else a.parent_Acc end) = a.id_acc
--ends here
   start with a.id_acc in (select id from to_show))
--Now lets use those top domains or root notes to render the hierarchy of each.
    SELECT a.id_acc
         , level as lvl
         , SYS_CONNECT_BY_PATH(ID_Acc, '/') "Path"
         , connect_by_isleaf "IsLeaf"
    FROM (Select id_acc, parent_acc from hSource) a
    connect by nocycle prior a.id_acc=a.parent_acc
    start with id_acc in  (Select ID_ACC from Top_Domain)


Share : facebook icon twitter icon
Recursive Query on a self referential table (not hierarchical)

Recursive Query on a self referential table (not hierarchical)


By : Funding Chain
Date : March 29 2020, 07:55 AM
will be helpful for those in need If you are not dealing with a large number of entries, the following solution might be suitable. The idea is to build the complete "id path" for each row and make sure the "current id" (in the recursive part) is not already in the path being processed:
(I removed the join to jobpath for testing purposes but the basic pattern should be the same)
code :
WITH JobPathTemp (JobId, ParentId, Level, id_path)
AS
(
  SELECT jobid, 
         parentid, 
         1 as level, 
         '|' + cast(jobid as varchar(max)) as id_path
  FROM job
  WHERE jobid = 1

  UNION ALL

  SELECT  j.JobId, 
          j.parentid, 
          Level + 1, 
          jpt.id_path + '|' + cast(j.jobid as varchar(max))
  FROM Job as j
    INNER JOIN JobPathTemp AS jpt ON j.jobid = jpt.parentid 
                                     AND charindex('|' + cast(j.jobid as varchar), jpt.id_path) = 0
)
SELECT *
FROM JobPathTemp
;
Sort hierarchical table CTE query

Sort hierarchical table CTE query


By : Pavlo Zhuk
Date : March 29 2020, 07:55 AM
Any of those help The hierarchyid datatype is able to represent hierarchical data, and already has the desired sorting order. If you can't replace your ParentID column, then you can convert to it on the fly:
(Most of this script is data setup, the actual answer is quite small)
code :
declare @t table (ID int not null,Name varchar(10) not null,ParentID int not null)
insert into @t(ID,Name,ParentID)
select 0,''          ,-1 union all
select 1,'Book'      ,0  union all
select 2,'App'       ,0  union all
select 3,'C#'        ,1  union all
select 4,'VB.NET'    ,1  union all
select 5,'Office'    ,2  union all
select 6,'PhotoShop' ,2  union all
select 7,'Word'      ,5

;With Sensible as (
    select ID,Name,NULLIF(ParentID,-1) as ParentID
    from @t
), Paths as (
    select ID,CONVERT(hierarchyid,'/' + CONVERT(varchar(10),ID) + '/') as Pth
    from Sensible where ParentID is null
    union all
    select s.ID,CONVERT(hierarchyid,p.Pth.ToString() + CONVERT(varchar(10),s.ID) + '/')
    from Sensible s inner join Paths p on s.ParentID = p.ID
)
select
    *
from
    Sensible s
        inner join
    Paths p
        on
            s.ID = p.ID
order by p.Pth
Query hierarchical items in a table

Query hierarchical items in a table


By : user2267459
Date : March 29 2020, 07:55 AM
To fix this issue We have a table in house which as hierarchical data in the following format. Items is the parent table with list of items. But, the relationship between items is maintained in std_item_rel table , CONNECT BY should allow for rather simple solutions, here:
code :
SELECT     std_item_down
FROM       std_item_rel
CONNECT BY std_item_up = PRIOR std_item_down
START WITH std_item_down = 123
Hierarchical Query to Self-Join Table Max 3 Times

Hierarchical Query to Self-Join Table Max 3 Times


By : Kang Yan
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further
there is a ton of information related to hierarchical queries, but nothing including self-joins AND hierarchical queries
code :
select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
  prior id as pid, prior detail1 as pdetail1,
  id as cid, detail1 as cdetail1,
  level as lvl, connect_by_isleaf as is_leaf
from table1
start with parentid is null
connect by prior id = parentid
select *
from (
  select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
    prior id as pid, prior detail1 as pdetail1,
    id as cid, detail1 as cdetail1,
    level as lvl, connect_by_isleaf as is_leaf
  from table1
  start with parentid is null
  connect by prior id = parentid
)
where is_leaf = 1;
      GPID GPDETA        PID PDETAI        CID CDETAI        LVL    IS_LEAF
---------- ------ ---------- ------ ---------- ------ ---------- ----------
         1 BLAH1           2 BLAH2           3 BLAH3           3          1
         1 BLAH1           2 BLAH2           4 BLAH4           3          1
         5 BLAH5           6 BLAH6           7 BLAH7           3          1
         5 BLAH5           8 BLAH8          10 BLAH10          3          1
         5 BLAH5           5 BLAH5           9 BLAH9           2          1
select gpid, gpdetail1,
  case lvl when 2 then cid else pid end as pid,
  case lvl when 2 then cdetail1 else pdetail1 end as pdetail1,
  case lvl when 2 then null else cid end as cid,
  case lvl when 2 then null else cdetail1 end as cdetail1
from (
  select connect_by_root (id) as gpid, connect_by_root(detail1) as gpdetail1,
    prior id as pid, prior detail1 as pdetail1,
    id as cid, detail1 as cdetail1,
    level as lvl, connect_by_isleaf as is_leaf
  from table1
  start with parentid is null
  connect by prior id = parentid
)
where is_leaf = 1;

      GPID GPDETA        PID PDETAI        CID CDETAI
---------- ------ ---------- ------ ---------- ------
         1 BLAH1           2 BLAH2           3 BLAH3 
         1 BLAH1           2 BLAH2           4 BLAH4 
         5 BLAH5           6 BLAH6           7 BLAH7 
         5 BLAH5           8 BLAH8          10 BLAH10
         5 BLAH5           9 BLAH9                   
SQL pivot table query and hookup query in hierarchical format

SQL pivot table query and hookup query in hierarchical format


By : Axr Brandal'z Madrid
Date : March 29 2020, 07:55 AM
will be helpful for those in need Consider a solution along the following lines (anything markedly different from this is frankly wasting your time):
code :
DROP TABLE IF EXISTS table1;

CREATE TABLE table1
(resource VARCHAR(12) NOT NULL
,slc_rank INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(resource,slc_rank)
);

INSERT INTO table1 VALUES
('Critical',1,3),
('Critical',2,3),
('Critical',3,6),
('Critical',4,12),
('High',1, 8),
('High',2, 8),
('High',3,12),
('High',4,24);

DROP TABLE IF EXISTS table2;

CREATE TABLE table2
(name VARCHAR(20) NOT NULL
,resource VARCHAR(12) NOT NULL
,slc_rank INT NOT NULL
,PRIMARY KEY(name,resource,slc_rank)
);

INSERT INTO table2 VALUES
('RAM','Critical',1),
('KIRAN','Critical',2),
('BASS','High',3),
('LOUT','Critical',4),
('chinna','High',1),
('lavanya','Critical',3),
('babau','High',4),
('rani','Critical',2);

SELECT x.*
     , y.val 
  FROM table2 x 
  JOIN table1 y 
    ON y.resource = x.resource 
   AND y.slc_rank = x.slc_rank;
+---------+----------+----------+-----+
| name    | resource | slc_rank | val |
+---------+----------+----------+-----+
| babau   | High     |        4 |  24 |
| BASS    | High     |        3 |  12 |
| chinna  | High     |        1 |   8 |
| KIRAN   | Critical |        2 |   3 |
| lavanya | Critical |        3 |   6 |
| LOUT    | Critical |        4 |  12 |
| RAM     | Critical |        1 |   3 |
| rani    | Critical |        2 |   3 |
+---------+----------+----------+-----+
Related Posts Related Posts :
  • In postgreSQL what char replaces one other char?
  • Get count of children from each parent.SQL Server 2012
  • Trouble With GROUP BY and JOIN on SQL
  • rails activerecord order in group
  • Invalid Column Name During SQL Data Read
  • sql program with Group by 2 attributes and 1 condition (Import)
  • Access SQL Select rows that have value in common with results of condition
  • Teradata SQL Performance Tuning: STRTOK_SPLIT_TO_TABLE: EXPLAIN Failed. 3738: String is longer than 31000 characters
  • do i need to rewrite the case statement for every field?
  • How to write dynamic SQL Query to copy items from table with proper MasterLevelId
  • Redshift: Find MAX in list disregarding non-incremental numbers
  • SQL Partition data within and outside of a Common Table Expression
  • PostgreSQL efficient count of number of rows WHERE parameter=TRUE
  • How to select data rows into columns w/out knowing all values
  • Summing a aggregate function
  • SQL query returning values outside the specified range
  • Scripting database SQL Server
  • Delete records and update quantity
  • Trouble executing a function in the where clause if condition is met
  • SQL: Bug in Joining two tables
  • Getting size of users group not working in Ruby
  • How can I iterate through characters in a SQL result value?
  • Write data of one column to unix file with data in another column of the same record as file name
  • How to select the first row of each group?
  • XML Schema totalDigits/fractionDigits vs. SQL precision/scale
  • There is an issue with my code here, can someone look?
  • TSQL turn table rows into columns with column name and value coming from a row
  • MIN MAX query with a twist
  • join multiple table
  • PHP SQL Select between 4 columns
  • SQLite regular Expressions regex get exact word by number
  • invalid Column Name
  • Query for last 8 week data
  • Left Join not matching where data exists - Temp Tabls SQL 2012
  • Checking if a value is IN a colon separated column with in a table
  • PostgreSQL constraint error
  • How to use 2 instances of the same table
  • How to format date column to get the day name in Oracle SQL?
  • Run a query based on output of another query
  • How to find particular dates between two rows which have dates in oracle?
  • Oracle SQL: return only max value
  • How to use GROUP BY command properly in Google Big Query?
  • How to echo special characters in batch file?
  • How to read JSON data from SQL Server?
  • Oracle SQL: Use IN operator with inline array
  • Set Hex Value using prepeared stmt in java
  • SonarQube 5.2 Upgrade failed
  • searching for microsoft sql server equivalent of postgresql form
  • How can I write SQL in Oracle in my case?
  • Retrieve records from same name table from multiple databases in single result set
  • Replace duplicate values only in consecutive records with NULL
  • NOT vs <> Operator in sql server
  • Concatenate calculated fields in SQL
  • For each unique value of some groupid column, how do I get rows with last 3 dates?
  • SQL CASE in JOIN returning extra rows
  • Need to find only three different values in multiple columns then sum using Oracle
  • sql - Find the ID of the most booked flight
  • SQL insert data dynamic column name from another table
  • Insert random data in Oracle table
  • Regex to split values in PostgreSQL
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co