March 7, 2007 at 1:34 pm
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 a table that I have:
id | pid | name
----------------
3 | 0 | parentcat
4 | 3 | childcat
5 | 4 | grandchildcat
thanks for any help!
Ryan
March 7, 2007 at 2:28 pm
Hello Ryan,
Please try this one
declare @<your local variable> smallint
set @<your local variable> = 3
select <your table name alias>..name as Child, <your table name alias1>.name as GrandChild
from <you table name> alias inner join <your table name> alias1 on alias.id = alias1.parentid
where alias.parentid = <your local variable>
Replace <your table name> with the relevant tablename.
Hope this helps your requirement.
Thanks
Lucky
March 7, 2007 at 2:34 pm
Not quite the results I need. I need parent child and grandchild to be in separate rows.
March 8, 2007 at 7:45 am
Depending on what you need from the results, you might review a technique like this one:
http://www.geekzilla.co.uk/View96264E4E-52EE-42BD-9494-2F33947A9063.htm
From your original post I can see how you stored the data, how would you like it to appear when you retreive it? That will dictate some of the methods you use. Alternatives include joined values like the solution lucky posted (which you may be able to use by joining on a case statement which returns the original id in each row), a stored procedure similar to the one mentioned in the article above, a set of views that are nested to provide children and grandchildren with the respective keys so you can join them in different ways as necessary, and probably a few others.
Hope that helps.
March 8, 2007 at 7:50 am
I found a solution. Heres what I have....
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
declare @id int
set @id = 3
select config_id, config_pid, config_name
from crm_map_config
where
config_id = @id
or config_pid = @id
or config_pid in (
select config_id
from crm_map_config
where config_pid=@id
)
Thanks for your help,
Ryan
March 8, 2007 at 1:05 pm
Great! If you find that your tree gets more than 3 levels, or a dynamic depth above three, you can use the other techniques to drill down a hierarchical data set. Enjoy!
March 8, 2007 at 1:24 pm
Heres what I have for unlimited children levels...
DECLARE @pid INT SET @pid = 3 --This get the parent you are looking for
DECLARE @Level INT
SET @Level = 1
DECLARE
@Hierarchy_List TABLE(
config_id INT,
config_pid INT,
config_name VARCHAR(50),
config_req INT,
Level INT)
INSERT
INTO @Hierarchy_List
SELECT
config_id
,config_pid
,config_name
,config_req
,@Level AS Level
FROM crm_map_config
WHERE config_id = @pid --This get the parent you are looking for
AND active = 1
WHILE (@@ROWCOUNT > 0) --now get all child/grandchild/grand-grandchild/etc.
BEGIN
SET @Level = @Level + 1
INSERT INTO @Hierarchy_List
SELECT
H.config_id
,H.config_pid
,H.config_name
,H.config_req
,@Level AS Level
FROM crm_map_config H (NOLOCK)
INNER JOIN @Hierarchy_List L ON H.config_pid = L.config_id
AND L.Level = @LEVEL -1
END
SELECT config_id, config_pid, config_name, config_req FROM @Hierarchy_List
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply