Thanks ... Now I can even use that query to create views. Akshay
On Wed, Aug 29, 2012 at 6:03 PM, witerat <[email protected]> wrote: > > > 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. > -- Akshay Mehta Ph. - 09302104522 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. 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.
