Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    17

    Unanswered: Select from same table

    Hi I have the following situation:

    tbl_resource

    Code:
    Id       -    parent_name         - childOf
    2                PC                   null
    3                Office               null
    4                Keyboard              2
    I have the following query:
    Code:
    select * from tbl_resource
    I will like to get parent_name for childOf value, so something like:
    Code:
    Select * from tbl_resource (if childOf != null ) 
    { get parent_name for childOf value)
    How do I do this in a query?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    something like
    select <my,column,list> from <mytablename> as A left join <mytablename> as B on B.childof = A.id;

    should do the trick, although that will only find the immediate parent

    HTH

  3. #3
    Join Date
    Jan 2008
    Posts
    17
    This is not exactly the solution I am looking for, because the table contains much data.

    any other alternative?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    that will return all parent ID's, including those with no parentID's
    to limit the results apply an appropriate 'where' clause
    and / or alter the join characteristic
    a simple join will not return those with no parentID

  5. #5
    Join Date
    Jan 2008
    Posts
    17
    Code:
    Select A.ID,A.Parent_name,A.ChildOf from testen as A left join testen as B on B.childof = A.ID;
    Gives:
    Code:
    ID	Parent_name	ChildOf
    -------------------------------------
    1	PC	           NULL
    2	Office	           NULL
    3	Keyboard	   2
    I would like to have something like:
    Code:
    ID	Parent_name	ChildOf     ChildParentName
    ------------------------------------------------------
    1	PC	           NULL           NULL
    2	Office	           NULL           NULL
    3	Keyboard	   2            Office
    Help with a query pleasssse?
    Maybe it is easier if I create extra field and not only store ChildOf but also ChildParentName during inserting/edit etc
    Last edited by internationalist; 02-07-08 at 11:38.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so adjust the query to include the column(s) you want.

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    What Healdem said.

    Code:
    Select A.ID,A.Parent_name,A.ChildOf  , A.Parent_name AS [PARENT of CHILD]from testen as A left join testen as B on B.childof = A.ID;
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  8. #8
    Join Date
    Jan 2008
    Posts
    17
    Code:
    ID	Parent_name	ChildOf     ChildParentName
    ------------------------------------------------------
    1	PC	           NULL           PC
    2	Office	           NULL           Office
    3	Keyboard	   2            Keyboard
    That is the result, which I dont want.

    but this did it
    Code:
    Select a.id,a.parent_name,a.childof , b.parent_name AS parent_name from testen as a left testen as b on b.id = a.childof;
    Thank you!
    Last edited by internationalist; 02-14-08 at 19:37.

  9. #9
    Join Date
    Feb 2008
    Posts
    2
    Please try this query:

    SELECT
    id
    ,parent_name
    FROM tbl_resource
    WHERE childof IS NOT NULL
    Last edited by JAILO0330; 02-16-08 at 08:13. Reason: Error on typing

  10. #10
    Join Date
    Feb 2008
    Posts
    2
    Sorry I wasnt able get the idea from the readof the problem:

    I think this will do the trick:

    SELECT
    a.id
    ,a.Parent_name
    ,a.ChildOf
    ,'ChildParentName' = b.Parent_name
    FROM tbl_resource a
    LEFT JOIN tbl_resource b
    ON a.ChildOf = b.ID
    Last edited by JAILO0330; 02-16-08 at 08:22. Reason: Typing Error

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jailo, you are allowed to say 'ChildParentName' = b.Parent_name in the SELECT list in mysql, but the results are not what you think they are

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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