I'm having a bit of a problem coming up with an HQL query, and wondered if someone could help me....

In my domain model, I have a Topic, User and Subscription.
A Topic has a set of Subscriptions against it, and each Subscription belongs to a User. A User can have many simultaneous Subscriptions and additionally has the concept of a default subscription.

Setting up the mappings and doing simple creats / finds has been fine.

However, I want to enforce that a Topic can not be deleted if it contains a default subscription for a subscriber with more than one subscription.

Using my domain model its easy: Ask the topic for its subscriptions, and for each subscription - get the subscriber, see if its the default subscription and if so see how many subscriptions it has.

The problem is, with a large number of subscriptions / subscribers, this could be very very inefficient.

So Im trying to come up with an HQL query to solve it more efficiently (i.e, given a Topic, I get back the subscriptions which are the default subscriptions of their subscriber - which itself has more than one subscription).

It sounds quite tricky. Further, Im using MySql v4.0.x so I cant use subqueries.....

Does anyone have any pointers that might help?

Thanks,

Dave