In all Firebird versions that I know about (I started with InterBase 5.6), you can do something like:

select t1.team_id, t2.team_id
from team t1
join team t2 on t1.team_id < t2.team_id
where not exists(select * from team_members tm1
where tm1.team_id = t1.team_id
and not exists(select * from team_members tm2
where tm2.team_id = t2.team_id
and tm2.user_id = tm1.user_id))
and not exists(select * from team_members tm2a
where tm2.team_id = t2.team_id
and not exists(select * from team_members tm1a
where tm1a.team_id = t1.team_id
and tm2a.user_id = tm1a.user_id))

I.e. find teams where team A doesn't have any player that team B doesn't have and team B doesn't have any player that team A doesn't have.

These are one of the cases were I find nested NOT EXISTS to be useful. Still, for performance reasons, I hope you don't have millions of teams in your team table...

Set

Ian A. Newby wrote:

> Hi Folks,
> An interesting SQL question...
>
> I have two tables, teams and team_members.
>
> for the sake of argument, these are the structures.
>
> team {
> team_id
> team_name
> )
>
> team_members (
> team_id
> user_id
> )
>
> What I want to do is find any teams which have the same members as
> another team...
>
> I think I could do it using 2.1 with the new list operator but
> unforunately, I'm using 2.0.
>
> Any ideas?
>
> Regards
> Ian Newby

[Non-text portions of this message have been removed]