"not exists" query

Hi,

I have a data tree with one parent table linked with relationships to three child  tables.

I would like to formulate a query where I can retrieve records from the parent table for which one of the child tables doesn't have any data, similarly to what I would do in SQL by using the NOT EXISTS condition.

How can I do this?

Thanks

Anna

You are meaning to find parent entries that don't have any child rows? Assuming so then I don't think there is any direct way to do this currently. Your best bet might be to use a view for the parent instead of a table, and incorporate a column in the view that has the child row count. Then you would be able to query on that column (for a value of zero).

Would this work for you?

Tim

I tried such a solution, but the perfomance was not satisfactory.

How are going doing this? I tested two approaches, one using NOT EXISTS, and one querying on the count and they are both very fast in my case (though using NOT EXISTS is admittedly slightly faster).

SELECT count(*)
FROM parent p
WHERE NOT EXISTS
  (SELECT *
    FROM child c
    WHERE c.parent_id = p.id)

SELECT count(*)
FROM parent p
WHERE 
  (SELECT count(*)
    FROM child c
    WHERE c.parent_id = p.id) = 0

This would suggest that incorporating the child row count into a view of the parent table shoudl give qood query performance, at least for medium sized tables.