SQL JOIN QUERY with more than one items in common: Find the franchise pairs that operate together in more than one location [closed]

Question

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

Answer 1

Something like this - use group by to gather all the "franchise pairs" and you count how many have locations..

SELECT X.fr, X.fr2, COUNT(X.loc) as count FROM 
(
  select T1.fr, T2.fr2, T1.loc 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
) AS X GROUP BY fr, fr2 HAVING count > 1;

NOTE: This relies on the inner query having distinct results - ie.. you can't have say "Best Western" and "Raddison" twice for the same location.