Help with Query

[Akshay Mehta]

Hi,

 

I have the following Table :

 

CREATE SEQUENCE IF NOT EXISTS GROUPIDS START WITH 101 INCREMENT BY 1;
CREATE TABLE Account_Group (
  Group_ID Integer DEFAULT NEXT VALUE FOR GROUPIDS,
  Group_Name varchar(100) NOT NULL UNIQUE,
  Parent Integer NOT NULL,
  Rule Integer NOT NULL DEFAULT 36,
  PRIMARY KEY (Group_ID)
);

 

INSERT INTO Account_Group (Group_ID,Group_Name,Parent,Rule) VALUES
 (101,'Primary',101,36),
 (102,'Income',101,2),
 (103,'Direct Income',102,3),
 (104,'Indirect Income',102,4),
 (105,'Sales Account',102,5),
 (106,'Expenses',101,6),
 (107,'Direct Expenses',106,7),
 (108,'Indirect Expenses',106,8),
 (109,'Purchase Account',106,9),
 (110,'Assets',101,10),
 (111,'Current Assets',110,11),
 (112,'Bank Accounts',111,12),
 (113,'Cash in Hand',111,13),
 (114,'Deposits (Asset)',111,14),
 (115,'Stock in Hand',111,15),
 (116,'Sundry Debtors',111,16),
 (117,'Fixed Assets',110,17),
 (118,'Investment',110,18),
 (119,'Loans & Advances (Asset)',110,19),
 (120,'Misc. Expenses',110,20),
 (121,'Liabilities',101,21),
 (122,'Loans (Liabilities)',121,22),
 (123,'Bank OCC A/c',122,23),
 (124,'Bank OD A/c',122,24),
 (125,'Secured Loans',122,25),
 (126,'Unsecured Loans',122,26),
 (127,'Current Liabilities',121,27),
 (128,'Duties & Taxes',127,28),
 (129,'Provisions',127,29),
 (130,'Sundry Creditors',127,30),
 (131,'Capital Account',121,31),
 (132,'Reserves & Surplus',131,32),
 (133,'Retained Earnings',131,33),
 (134,'Branch/Division',121,34),
 (135,'Suspense A/c',121,35),
 (136,'Drawings A/c',108,8);

Can anyone help me in writing a single query to find all the Children and Grand-Children ...... of any particular group (say Group_id: 109).

 

Thanks
Akshay

[Brian]

See the section in the documentation labeled "Recursive Queries" just under the UUID anchor:
http://www.h2database.com/html/advanced.html#uuid

(Comitters: Looks like the recursive queries section in advanced.html isn't anchored/indexed at the top)

You'll have to be very careful.  For example: 109 didn't have any children in the example data you provided.
Also - if there are loops in your DATA (101's parent is 101? - that's bad loop!) - then you'll DOS yourself.

WITH LINK(GROUP_ID, GROUP_NAME, LEVEL) AS (
    SELECT GROUP_ID, GROUP_NAME, 1 FROM ACCOUNT_GROUP WHERE PARENT=101 AND GROUP_ID <> PARENT
    UNION ALL
    SELECT ACCOUNT_GROUP.GROUP_ID, IFNULL(LINK.GROUP_NAME || '/', '') || ACCOUNT_GROUP.GROUP_NAME, LEVEL + 1
    FROM LINK INNER JOIN ACCOUNT_GROUP ON LINK.GROUP_ID = ACCOUNT_GROUP.PARENT
)
SELECT GROUP_ID,GROUP_NAME,LEVEL FROM LINK WHERE GROUP_NAME IS NOT NULL ORDER BY GROUP_ID;

-Brian

[Akshay Mehta]

Thanks a Lot! That Worked.

 

regards

Akshay

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/zGZcOUQ_JFEJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

--
Akshay Mehta
Ph. - 09302104522

[witerat]

Thanks
Akshay


<code>
  Select * from (Account_Group left join Account_Group p on (Account_group.parent=parent.group_id))left join Account_group gp on p.parent=gp.group_id
  where :a_group=p.group_id or :a_group=gp.group_id
</code>

[Akshay Mehta]

Thanks ... Now I can even use that query to create views.

 

Akshay

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/8OPE17mH_zkJ.

To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.