Help with a hierarchy query or procedure

I have a table with a parent, child, and grandchild relationship. Can anyone help me with a query that will return the child and grandchild of a parent?

Heres my table:

id pid name
----------
1 0 UntID
2 0 Vin Number
3 0 Make
4 3 Model
5 4 Model Number
6 0 Model Year
7 0 Vehicle Type
8 0 Odometer Miles

When I select 3 as the id I need these results:

id pid name
----------
3 0 Make
4 3 Model
5 4 Model Number

Thanks for any help!

Ryan

ive successfully used a technique very much like this:http://www.developerfusion.co.uk/show/4633/2/

By tracking your "hierarchy path" (lineage), you can easily pull out an entire branch (Parent/child/grandchild...) of your tree.
By tracking your "hierarchy depth", you can also limit how much of a branch your pull out.

I prefer this approch over something recursive.

If you implement this, your data would end up like this:

id pid name lineage----------1 0 UntID /1/2 0 Vin Number /2/3 0 Make /3/4 3 Model /3/4/5 4 Model Number /3/4/5/6 0 Model Year /6/7 0 Vehicle Type /7/8 0 Odometer Miles /8/

And you query would look like this:

SELECT *FROM someTableWHERE lineageLIKE'/3/%';

|||

This SQL command will get all information required for all tables.

Child, Parent, GranShild are ur tables

select Parent.Id, Child.Id,GrandShild.Id from Parent inner join Child on Parent.Id = Child.Pid inner join GrandChild on Child.id = GrandChild.Pid

where ...

Inform me if this helps

|||I cant change the structure I have. So I need something different. Thanks anyway!|||Try using the command i showed u, doesnt force u to change any structure|||But theres only one table|||provide me with ur table fields, in order to write for u the sql query|||

Hi there,

this query works

SELECT
PARENT.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3
UNION
--CHILDS RECORDS
SELECT
CHILDS.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3
UNION
--GRANDCHILDS RECORDS
SELECT
GRANDCHILDS.*
FROM
PARENT
INNER JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
INNER JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3

I hope it helps

Regards,

Fernando

|||

Thanks FerVitale, that works the best so far. The only problem is that I also have parent and child relationships without grandchildren and if I set that parent as the parentID I get no records. Any thoughts?

Ryan

|||Remove the inner join and replace them with left outer join it should work fine|||

Cool, now the only problem is that if there is a parent/child but no grandchild, the top row is all nulls. any ideas?

Ryan

|||

dear Ryan Try this a bit

Remove all unions just keep the folowing

SELECT
PARENT.*
FROM
PARENT
left outer JOIN PARENT CHILDS ON PARENT.ID = CHILDS.PID
Left outer JOIN PARENT GRANDCHILDS ON CHILDS.ID = GRANDCHILDS.PID
WHERE
PARENT.ID = 3

|||

That only gives me the parent and child in a single row. I need a row for each relationship. I found something else that works perfectly.

declare @.idintset @.id = 3select config_id, config_pid, config_namefrom crm_map_configwhereconfig_id = @.idor config_pid = @.idor config_pidin ( select config_id from crm_map_config whereconfig_pid=@.id)

Thanks for everyones help,
Ryan

|||

Hi,

this script will work, no matter how deep is your parent/child/grandchild relation:

DECLARE

@.Hierarchy_ListTABLE(

ID

INT,

PID

INT,NAMEVARCHAR(50),LevelINT)

DECLARE

@.LevelINT

SET

@.Level= 1

INSERT

INTO @.Hierarchy_ListSELECT*,

@.Level

ASLevelFROM dbo.HierarchyWHERE ID= 3 --This get the parent you are looking for

WHILE(@.@.ROWCOUNT> 0) --now get all child/grandchild/grand-grandchild/etc.BEGINSET @.Level= @.Level+ 1INSERTINTO @.Hierarchy_ListSELECT H.*,

@.Level

ASLevelFROM dbo.Hierarchy H(NOLOCK)INNERJOIN @.Hierarchy_List LON H.PID= L.IDAND L.Level= @.LEVEL-1ENDSELECT id, pid, [Name]FROM @.Hierarchy_List

Hope this helps

|||

kpeguero,

Thanks, thats even better!

Ryan