query for single table with parent/child relations 1

[gacaccia]

i have a single "tasks" table with a taskID primary key and a parentID field. the parentID field is an "informal" foreign key in that i've not establish a formal relation between the taskID field and the parentID field.

tasks
---------------
taskID
parentID
task
...


i need a query that will return all "parent" task records that only have one child record where the child record itself is not a parent of anything else.

i know how to make a query that will return all parent tasks with child tasks, but i'm not sure how to limit it to only parents tasks with one child task.

thanks for any help,

glenn

 

[hmckillop]

 select count(*) as counter, parentID
from myTable
group by parentID
having count(*) = 1) tmp )

this should help you limit to only one item. Dont have time to test, but then perform other part of your query

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gacaccia]

thanks, that helped. my final query looks like...

select * from tasks as ct inner join tasks as pt on ct.parentID = pt.taskID 
where pt.taskID in (select parentID from tasks group by parentID having count(*) = 1)