counting the number of occurences from one table

[Sarky78]

Hi,

This is going to be a bit difficult to explain so i hope this comes across ok.

so here we go. i have one table that has the following structure:

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
9                7         Childoffence3
10               9         GrandChild5

And the offenceID is then used as a foreign key in another table. i need to be able to query this other table and find the number of occurances of ALL of the grandchildren within this other table, and then return the parentOffence from the above table.

so, if i have the offenceid 10 in my other table, i want to be able to return the number of times anything within the same parent group (ParentOffence2) occures within the other table.

at the moment i am banging my head against the table, hoping some clever people out there can help. ok that's enough sucking up now

TIA

Tony

 

[LNBruno]

What's the other table structure? What do you have so far?

< M!ke >
[small]Don't believe everything you think.[/small]

 

[Sarky78]

The other table structure is literally just an OffenceID in another table and a bunch of other fields. so at the moment i have:

SELECT DISTINCT a.AppealID, a.OffenceID, offences.OffenceName, offences.ParentID
FROM tblAppeals a, tblOffences as offences
WHERE a.OffenceID = offences.OffenceID
AND a.OffenceID = offences.OffenceID

which isn;t the best of starts i know. what our dev guy did was to get that list and then loop over it in asp, which i want to get away from if poss.

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=5322

"NOTHING is more important in a database than integrity." ESquared