Query on Parent-Child based table

[Massoud]

Hi
I have a parent-child base table (accounts) with the following fields:
- id
- parentid
- name
every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child)
different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....

I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.

Thanks again for the help.

 

[ByteMyzer]

Try something like:

SELECT A1.*
FROM accounts AS A1 LEFT JOIN accounts AS A2 ON A1.id = A2.parentid
WHERE A2.parentid IS NULL;

 

[KenHigg]

I think you'll need to queries to do this;

Query one is a simple totals query where you group parentid's.

In the second query bring in the original table and the first query and do a link on table!id to query1!parentid and have the second query only return rows where the parentid field from the 1st query is null...


ken

 

[Massoud]

Thank you very much ByteMyzer . Fast and exact.