Get parent Record 1

[Sarky78]

Hi All,

Really hoping that someone here can help me with this. I designed a database table to hold a load of related records, so they could all be associated together. It is basically a parent child, grandchild relationship within this table.

Now what i need to be able to do, is to query this table, with either a parent, a child or a grandchild ID, and for it to then return the parents text field.

The data structure is:

OffenceID     ParentID     OffenceName
1                0         ParentOffence1
2                1         ChildOffence1
3                2         GrandChild1
4                2         GrandChild2
5                2         GrandChild3
6                0         ParentOffence2
7                6         ChildOffence2
8                7         GrandChild4

So, with the above data, I want to be able to query the table with an OffenceID of say 8, and for it to return "ParentOffence2". I thought I'd be able to get around this by joining the table to itself three times, but that isn't working.

Anyone know of a clever way of doing this?

Thanks in advance

Tony

 

[gmmastros]

>> I thought I'd be able to get around this by joining the table to itself three times, but that isn't working.

I would have thought that would work.

Here's a sample script (that uses a hardcoded table variable) that you can play around with.

Declare @Temp Table(OffenceId Int, ParentId Int, OffenceName VarChar(20))

Insert Into @Temp Values(1,0,'ParentOffence1')
Insert Into @Temp Values(2,1,'ChildOffence1')
Insert Into @Temp Values(3,2,'GrandChild1')
Insert Into @Temp Values(4,2,'GrandChild2')
Insert Into @Temp Values(5,2,'GrandChild3')
Insert Into @Temp Values(6,0,'ParentOffence2')
Insert Into @Temp Values(7,6,'ChildOffence2')
Insert Into @Temp Values(8,7,'GrandChild4')

Select Coalesce(Parent.OffenceName, Child.OffenceName, Grandchild.OffenceName), * 
From   @Temp As Grandchild
       Left Join @Temp As Child
         On Grandchild.ParentId = Child.OffenceId
       Left Join @Temp As Parent
         On Child.ParentId = Parent.OffenceId

Note that this will only work with 3 levels. Add a fourth level and you will need to change the query.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sarky78]

George,

Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you! Thank you!

It works for the lower level(GrandChild), but not for the other levels, which i think is sufficient for this system.

Thanks for your quick response, and have a star

Tony

 

[gmmastros]

>> not for the other levels

In what way does it 'not work' for the other levels?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sarky78]

George,

this is my query

SELECT Coalesce(Parent.OffenceName, Child.OffenceName, GrandChild.OffenceName)
FROM ((tblOffences GrandChild LEFT JOIN tblOffences Child ON GrandChild.ParentID=Child.OffenceID) LEFT JOIN tblOffences Parent ON Child.ParentId = Parent.OffenceID)
WHERE Parent.OffenceID = 1

the code works up the tree, from grandchild, to child, but then fails on the parent, but i don't think is too much of a bother, as i think the client has asked for the parent not to be selectable, so the code will work for what i need it to do, which is to show the parent, for the children/grandchildren

Thanks

Tony

 

[gmmastros]

What database are you using? Access? Besides, I thought you wanted to specify the OffenceId for the grandchild.

Where Grandchild.OffenceId = 8

I know why you are not getting the results you are looking for. It's because you have a where clause involving the 'right' table in a left join. If you specify the grandchild id (=8) instead of the parent id, I think you would have better luck.

Anyway, try this to see if the results are any better.

SELECT Coalesce(Parent.OffenceName, Child.OffenceName, GrandChild.OffenceName)
FROM   ((tblOffences GrandChild 
       LEFT JOIN tblOffences Child ON GrandChild.ParentID=Child.OffenceID) 
       LEFT JOIN tblOffences Parent ON Child.ParentId = Parent.OffenceID [!]And Parent.OffenceId = 1[/!])

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom