--- In firebird-support@yahoogroups.com, "Ian A. Newby" <ian@...> 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?

I can think of a stored procedure:

create procedure teamlist
(
team_id integer
)
(
memberlist varchar(1000)
)
as
declare variable tmp integer
begin
for
select userid
from team_members
where team_id = :team_id
into :tmp
do
begin
memberlist = memberlist || ',' || tmp;
end
suspend;
end
^

That is effectively your list, the next part would be pretty expensive
(n^2 reads of team table + n^2 indexed reads of team_members)

select t.*
from team t
left join teamlist(t.team_id) tl on (1=1)
where exists
(
select *
from team t2
left join teamlist(t2.team_id) tl2 on (1=1)
where t.teamid > t2.team_id
and tl.memberlist = tl2.memberlist
)

Alternatively, a much cheaper query could return which teamlist is
duplicated.

select tl.memberlist, count(*)
from team t
left join teamlist(t.team_id) tl on (1=1)
group by 1
having count(*) > 1


Adam