I have a table called dfmt that lists the location, revenue and franchise. I want to find the franchise pairs that operate together in more than one location.
So far, I have a query that finds the franchise pairs that operate in the same location:
select T1.fr, T2.fr2 from dfmt T1 join (select fr as fr2, loc as loc2 from dfmt) as T2 on T1.fr < T2.fr2 and T1.loc = T2.loc2 order by loc;
I do not know how to go from here to find the franchise pairs that operate together in only more than one location.
Another query that may be useful that Finds the franchise that generates the maximum revenue in more than one location.
select fr, count(*) from tst2 where rev in (select max(rev) from tst2 group by loc) group by fr having count(*)>1; enter code here