Select from same table

internationalist · #1 (**permalink**) 02-07-08, 02:36

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?

healdem · #2 (**permalink**) 02-07-08, 03:55

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

internationalist · #3 (**permalink**) 02-07-08, 09:39

This is not exactly the solution I am looking for, because the table contains much data.

any other alternative?

healdem · #4 (**permalink**) 02-07-08, 10:12

?
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

internationalist · #5 (**permalink**) 02-07-08, 10:34

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

healdem · #6 (**permalink**) 02-07-08, 11:33

so adjust the query to include the column(s) you want.

Marvels · #7 (**permalink**) 02-11-08, 07:36

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;

internationalist · #8 (**permalink**) 02-14-08, 18:32

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!

JAILO0330 · #9 (**permalink**) 02-16-08, 07:10

Please try this query:

SELECT
id
,parent_name
FROM tbl_resource
WHERE childof IS NOT NULL

JAILO0330 · #10 (**permalink**) 02-16-08, 07:21

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

r937 · #11 (**permalink**) 02-16-08, 09:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech