Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: child-parent relationship query

    Hi there,

    I need help to make a sql query.

    Table data like this
    Here I=parent and S= school

    Table name-customer

    Customerid custname flag parentid
    1 A I 0
    2 P I 0
    3 B S 1
    4 Y S 5
    5 X I 0
    6 z S 5
    7 c S 1
    8 R S 2
    9 Q S 2


    output should be like this:
    Customerid custname flag parentid
    1 A I 0
    3 B S 1
    7 c S 1
    2 P I 0
    8 R S 2
    9 Q S 2
    5 X I 0
    4 Y S 5
    6 z S 5

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    apart from ordering by custname what is different between the two tables? how is this a parent/child relationship?

  3. #3
    Join Date
    Mar 2004
    Posts
    4

    in detail

    Here last column name parentid carry information of customerid of his parent.

    customerid,customername,flag,parentid are four column of table.

    1)start with customerid who is parent(You can know by its flag I=parent and S=child).
    2)Retrive all child of that customerid.(mean all customerid whose parentid =selected customerid).Sort them by name

    repeat (1) and (2) for all customer id who is parent.

    i can understand what to do but i can't convert that logic in SQL form.

    Thanks.

  4. #4
    Join Date
    Mar 2004
    Posts
    4

    I got answer

    select A.customerid,A.customername,A.parentcustomerid from customer as A,
    customer as B where ( A.parentcustomerid = B.customerid and B.schoolflag ='I')
    order by A.parentcustomerid;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •