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

Help selecting the proper child record

Good Morning,
I have a person table with personID. I have a personRate table with
personID, rateID, and effectiveDate.
I need to select fields from personRate, but I want the fields from the
proper record.

I need the one child record that has the most current date of the largest
rateID.

For example a person may have many rate records. I need the record that has
the most current date of the largest rateID they have. Does that make
sense?

I am making a view that has data from both tables. I need to display the
most current rate info.

Any ideas? TIA ~ CKHi CK,

I kind of guessed on the DDL but something like the select statement below
(after the Creates and Inserts) should do it for you.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table PersonRate(
RateID int identity(1,1) Primary Key,
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')

insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')

select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
from Person p
inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
from PersonRate pr
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
"CK" <c_kettenbach@.hotmail.com> wrote in message
news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
> Good Morning,
> I have a person table with personID. I have a personRate table with
> personID, rateID, and effectiveDate.
> I need to select fields from personRate, but I want the fields from the
> proper record.
> I need the one child record that has the most current date of the largest
> rateID.
> For example a person may have many rate records. I need the record that
> has the most current date of the largest rateID they have. Does that make
> sense?
> I am making a view that has data from both tables. I need to display the
> most current rate info.
> Any ideas? TIA ~ CK|||See changes below. Also not every Person has a PersonRateRecord but I need
all the persons so I am thinking LEFT OUTER JOIN on PersonRate.

Thanks for the pointers!!

"DickChristoph" <dchristo99@.yahoo.com> wrote in message
news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
> Hi CK,
> I kind of guessed on the DDL but something like the select statement below
> (after the Creates and Inserts) should do it for you.
> Create Table Person(
> PersonID int identity(1,1) Primary Key,
> PersonName varchar(50))
> Create Table PersonRate(
*****PersonRateID int identity(1,1) Primary Key,
***** RateID int references Rate(RateID),
> PersonID int references Person(PersonID),
> Rate decimal(9,2),
> EffectiveDate smalldatetime)

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

> insert Person(PersonName) values ('Fred')
> insert Person(PersonName) values ('Barney')
> insert Person(PersonName) values ('Wilma')
>
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>
> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
> from Person p
> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
> from PersonRate pr
> inner join (select PersonID, Max(RateID) as MaxRateID
> from PersonRate
> group by PersonID) pr1
> on pr.personid = pr1.personid
> and pr.rateid = pr1.maxRateID) pp
> on p.PersonID = pp.Personid
> --
> -Dick Christoph
> "CK" <c_kettenbach@.hotmail.com> wrote in message
> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>> Good Morning,
>> I have a person table with personID. I have a personRate table with
>> personID, rateID, and effectiveDate.
>> I need to select fields from personRate, but I want the fields from the
>> proper record.
>>
>> I need the one child record that has the most current date of the largest
>> rateID.
>>
>> For example a person may have many rate records. I need the record that
>> has the most current date of the largest rateID they have. Does that
>> make sense?
>>
>> I am making a view that has data from both tables. I need to display the
>> most current rate info.
>>
>> Any ideas? TIA ~ CK
>>|||Hi CK

Alrighty then how about this. By the way I think the PersonRate.Rate column
is not where it should be. Probably it belongs in the Rate table but I don't
know what that column really means. You left it in PersonRate so I left it
in PersonRate.

Create Table Person(
PersonID int identity(1,1) Primary Key,
PersonName varchar(50))

Create Table Rate(
RateID int identity(1,1) Primary Key,
RateType varchar(20))

Create Table PersonRate(
PersonRateID int identity(1,1) Primary Key,
RateID int references Rate(RateID),
PersonID int references Person(PersonID),
Rate decimal(9,2),
EffectiveDate smalldatetime)

insert Person(PersonName) values ('Fred')
insert Person(PersonName) values ('Barney')
insert Person(PersonName) values ('Wilma')

insert Rate(RateType) values ('A')
insert Rate(RateType) values ('B')
insert Rate(RateType) values ('C')

--Fred
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,1,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,1,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,1,12,'3/1/2005')

--Barney
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(1,2,10,'1/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(2,2,11,'2/1/2005')
insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
values(3,2,12,'3/1/2005')

--No PersonRate For Wilma

select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
pp.Rate
from Person p
left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
r.RateType, pr.Rate
from PersonRate pr
inner join Rate r
on pr.RateID = r.RateID
inner join (select PersonID, Max(RateID) as MaxRateID
from PersonRate
group by PersonID) pr1
on pr.personid = pr1.personid
and pr.rateid = pr1.maxRateID) pp
on p.PersonID = pp.Personid

--
-Dick Christoph
dchristo@.mn.rr.com
612-724-9282
"CK" <c_kettenbach@.hotmail.com> wrote in message
news:5HEUf.62662$dW3.28210@.newssvr21.news.prodigy. com...
> See changes below. Also not every Person has a PersonRateRecord but I
> need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.
> Thanks for the pointers!!
> "DickChristoph" <dchristo99@.yahoo.com> wrote in message
> news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
>> Hi CK,
>>
>> I kind of guessed on the DDL but something like the select statement
>> below (after the Creates and Inserts) should do it for you.
>>
>> Create Table Person(
>> PersonID int identity(1,1) Primary Key,
>> PersonName varchar(50))
>>
>> Create Table PersonRate(
> *****PersonRateID int identity(1,1) Primary Key,
> ***** RateID int references Rate(RateID),
>> PersonID int references Person(PersonID),
>> Rate decimal(9,2),
>> EffectiveDate smalldatetime)
>>
> Create Table Rate(
> RateID int identity(1,1) Primary Key,
> RateType varchar(20))
>
>> insert Person(PersonName) values ('Fred')
>> insert Person(PersonName) values ('Barney')
>> insert Person(PersonName) values ('Wilma')
>>
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
>>
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>>
>>
>> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
>> from Person p
>> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
>> from PersonRate pr
>> inner join (select PersonID, Max(RateID) as MaxRateID
>> from PersonRate
>> group by PersonID) pr1
>> on pr.personid = pr1.personid
>> and pr.rateid = pr1.maxRateID) pp
>> on p.PersonID = pp.Personid
>>
>> --
>> -Dick Christoph
>> "CK" <c_kettenbach@.hotmail.com> wrote in message
>> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>>> Good Morning,
>>> I have a person table with personID. I have a personRate table with
>>> personID, rateID, and effectiveDate.
>>> I need to select fields from personRate, but I want the fields from the
>>> proper record.
>>>
>>> I need the one child record that has the most current date of the
>>> largest rateID.
>>>
>>> For example a person may have many rate records. I need the record that
>>> has the most current date of the largest rateID they have. Does that
>>> make sense?
>>>
>>> I am making a view that has data from both tables. I need to display
>>> the most current rate info.
>>>
>>> Any ideas? TIA ~ CK
>>>
>>
>>|||Rate is a $ amount , rateType is string indicating what the $ amount is for.
Like a "raise", "offer" , "request". Just some entities this company uses.
Yes it is in the correct place. Thanks a lot. Max didn't give me the desired
result. I use SELECT TOP 1.

here's what worked. I added a couple other tables as i needed values from
them as well

SELECT vC.*, CR.EffectiveDate, rt.TypeName, jc.CategoryName,
CASE
WHEN CR.Rate IS NULL THEN CAST('None' AS char(4))
ELSE CAST(CR.Rate AS varchar(6))
END AS Rate
FROM vCandidates vC
LEFT OUTER JOIN CandidateRate CR
ON vC.CandidateID = CR.CandidateID
AND CR.CandidateRateID IN
(
SELECT TOP 1 CandidateRateID
FROM CandidateRate
WHERE RateTypeID < 4 AND CandidateID = CR.CandidateID
ORDER BY RateTypeID DESC, EffectiveDate DESC
)
LEFT OUTER JOIN RateType rt ON rt.RateTypeID = CR.RateTypeID
LEFT OUTER JOIN Innova.dbo.JobCategory jc ON jc.JobCatID = CR.JobCatID

"DickChristoph" <dchristo99@.yahoo.com> wrote in message
news:dYEUf.18266$iR1.4639@.tornado.rdc-kc.rr.com...
> Hi CK
> Alrighty then how about this. By the way I think the PersonRate.Rate
> column is not where it should be. Probably it belongs in the Rate table
> but I don't know what that column really means. You left it in PersonRate
> so I left it in PersonRate.
> Create Table Person(
> PersonID int identity(1,1) Primary Key,
> PersonName varchar(50))
> Create Table Rate(
> RateID int identity(1,1) Primary Key,
> RateType varchar(20))
> Create Table PersonRate(
> PersonRateID int identity(1,1) Primary Key,
> RateID int references Rate(RateID),
> PersonID int references Person(PersonID),
> Rate decimal(9,2),
> EffectiveDate smalldatetime)
>
> insert Person(PersonName) values ('Fred')
> insert Person(PersonName) values ('Barney')
> insert Person(PersonName) values ('Wilma')
> insert Rate(RateType) values ('A')
> insert Rate(RateType) values ('B')
> insert Rate(RateType) values ('C')
> --Fred
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(1,1,10,'1/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(2,1,11,'2/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(3,1,12,'3/1/2005')
> --Barney
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(1,2,10,'1/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(2,2,11,'2/1/2005')
> insert PersonRate(RateID, PersonID, Rate, EffectiveDate)
> values(3,2,12,'3/1/2005')
> --No PersonRate For Wilma
>
> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.RateType,
> pp.Rate
> from Person p
> left outer join (select pr.PersonID, pr.EffectiveDate, pr.RateID,
> r.RateType, pr.Rate
> from PersonRate pr
> inner join Rate r
> on pr.RateID = r.RateID
> inner join (select PersonID, Max(RateID) as MaxRateID
> from PersonRate
> group by PersonID) pr1
> on pr.personid = pr1.personid
> and pr.rateid = pr1.maxRateID) pp
> on p.PersonID = pp.Personid
>
> --
> -Dick Christoph
> dchristo@.mn.rr.com
> 612-724-9282
> "CK" <c_kettenbach@.hotmail.com> wrote in message
> news:5HEUf.62662$dW3.28210@.newssvr21.news.prodigy. com...
>> See changes below. Also not every Person has a PersonRateRecord but I
>> need all the persons so I am thinking LEFT OUTER JOIN on PersonRate.
>>
>> Thanks for the pointers!!
>>
>> "DickChristoph" <dchristo99@.yahoo.com> wrote in message
>> news:XuEUf.18265$iR1.4137@.tornado.rdc-kc.rr.com...
>>> Hi CK,
>>>
>>> I kind of guessed on the DDL but something like the select statement
>>> below (after the Creates and Inserts) should do it for you.
>>>
>>> Create Table Person(
>>> PersonID int identity(1,1) Primary Key,
>>> PersonName varchar(50))
>>>
>>> Create Table PersonRate(
>> *****PersonRateID int identity(1,1) Primary Key,
>> ***** RateID int references Rate(RateID),
>>> PersonID int references Person(PersonID),
>>> Rate decimal(9,2),
>>> EffectiveDate smalldatetime)
>>>
>>
>> Create Table Rate(
>> RateID int identity(1,1) Primary Key,
>> RateType varchar(20))
>>
>>
>>> insert Person(PersonName) values ('Fred')
>>> insert Person(PersonName) values ('Barney')
>>> insert Person(PersonName) values ('Wilma')
>>>
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(1,12,'3/1/2005')
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(2,12,'3/1/2005')
>>>
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,10,'1/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,11,'2/1/2005')
>>> insert PersonRate(PersonID, Rate, EffectiveDate) values(3,12,'3/1/2005')
>>>
>>>
>>> select p.PersonID, p.PersonName, pp.EffectiveDate, pp.RateID, pp.Rate
>>> from Person p
>>> inner join (select pr.PersonID, pr.EffectiveDate, pr.RateID, pr.Rate
>>> from PersonRate pr
>>> inner join (select PersonID, Max(RateID) as MaxRateID
>>> from PersonRate
>>> group by PersonID) pr1
>>> on pr.personid = pr1.personid
>>> and pr.rateid = pr1.maxRateID) pp
>>> on p.PersonID = pp.Personid
>>>
>>> --
>>> -Dick Christoph
>>> "CK" <c_kettenbach@.hotmail.com> wrote in message
>>> news:Z1CUf.58897$Jd.37489@.newssvr25.news.prodigy.n et...
>>>> Good Morning,
>>>> I have a person table with personID. I have a personRate table with
>>>> personID, rateID, and effectiveDate.
>>>> I need to select fields from personRate, but I want the fields from the
>>>> proper record.
>>>>
>>>> I need the one child record that has the most current date of the
>>>> largest rateID.
>>>>
>>>> For example a person may have many rate records. I need the record that
>>>> has the most current date of the largest rateID they have. Does that
>>>> make sense?
>>>>
>>>> I am making a view that has data from both tables. I need to display
>>>> the most current rate info.
>>>>
>>>> Any ideas? TIA ~ CK
>>>>
>>>
>>>
>>
>>