Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2009
    Posts
    30

    Question Unanswered: SQL Query -- help needed

    Hello,

    I have one table as follows...

    Code:
    contractID parentcontractID
        1		1
        2		1
        3		2
        4		4
    I want to do the query which will give me output cumulative. means if my contractID is 3 then the query should return me

    Code:
    contractID parentcontractID
        1		1
        2		1
        3		2
    because contractID 3 have 2 as parentcontractID and contractID 2 have 1 as parentcontract ID. So query should return me the above output.

    please help me

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Take a look on the board here for recursion. Also, shouldn't the contracts that do not have a parent, have the parent listed as null? Rather than listing themselves as the parent?
    Dave

  3. #3
    Join Date
    Apr 2009
    Posts
    30
    Hello dav1mo,

    thanks for your reply. I'm not getting you means what exactly you wanna to say. So can you please explain ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look at example D here:
    WITH common_table_expression (Transact-SQL)

    Normally an item without a parent has a parent of NULL, but you can adjust the code to suit your data easily enough.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Normally an item without a parent has a parent of NULL
    and the reason for this choice is simply because you cannot declare the FK with some other value

    most people actually declare the FKs that are used as FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2009
    Posts
    30
    Hello all,

    Can anyone tell me SQL query for this ??

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you read the link? Have a go at writing this query by adapting example D to suit your table and column names and post it here if you struggle.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    and the reason for this choice is simply because you cannot declare the FK with some other value

    most people actually declare the FKs that are used as FKs
    Sorry Rudy - I don't actually understand this.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    Sorry Rudy - I don't actually understand this.
    okay, do an experiment

    create a table like this --
    Code:
    CREATE TABLE hier
    ( id INTEGER NOT NULL PRIMARY KEY
    , name VARCHAR(9) NOT NULL
    , parentid INTEGER 
    )
    now do either of these tests

    1. declare the FK for parentid and then try to insert values such as 0 for parentid or the same value as the id (as earlier in this thread)

    2. insert those values first and then try to declare the FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2009
    Posts
    30
    Hello All,

    Many thx for your replies....

    I'll make the thing simple for you.

    I have one table as follows...

    Code:
    contractID parentcontractID
        1		   1
        2		   1
        3		   2
        4		   4
    contractID as primark key.

    I want the query which will give me output cumulatively, means if my contractID is 3 then the query should return me

    Code:
    contractID parentcontractID
        1		  1
        2		  1
        3		  2
    because contractID 3 have 2 as parentcontractID, contractID 2 have 1 as parentcontractID and contractID 1 have 1 itself as parentcontractID. So query should return me the above output.

    I have tries with this query but I haven't got the desired output.

    select a.*, b.* from [contract] a, [contract] b where a.contractID = b.parentcontractID and a.contractID=3
    Last edited by shende.tejas; 06-05-10 at 01:26.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by shende.tejas View Post
    I'll make the thing simple for you.
    We understand. This is a common requirement. The link I posted gives you the solution. Try it.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Rudy - I understand now. It wasn't so much I didn't understand the concept, I just had no clue what you were trying to say!

  13. #13
    Join Date
    Apr 2009
    Posts
    30
    Hello pootle,

    Thanks for the link. I have seen the link and tried. bt still I'm in trouble. So can u pls give me some clue.

  14. #14
    Join Date
    Apr 2009
    Posts
    30
    Hello
    guyes pls help me..

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by shende.tejas View Post
    I have seen the link and tried.
    Post what you came up with and we'll help you correct it.

Posting Permissions

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