On Tuesday, August 28, 2012 9:36:26 PM UTC+12, Akshay wrote: > > 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 > > > <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> >
-- 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
