Unanswered: Quering a table on itself to find other counts
I have a blank spot with a query, so any suggestions are welcome, as it is doing my head in.
I have a table which contains CustomerIDs and assoicated MemberIDs (which in fact are also rebranded CustomerIDs). In other words what customers belong to another customers group, and a customer cannot belong to their own group. So the typical structure is: -
As mentioned, getting the first two columns is easy enoough. The problem is getting the third column. This column represent members of a customer who are also members of another customer.
So, looking at the sample data above: -
Customer 1 has 5 members and 3 are overlapping, i.e. also belong to other customers, in this case Customer 2
Customer 2 has 4 members and 3 are overlapping, i.e. also belong to other customers, in this case Customer 1
Customer 3 has 2 members and 0 are overlapping, i.e. none belong to other customers.
It sounds simple I know, but my brain is mangled due to other rather annoying and overcomplicated issues with this existong database.
Well - my brain is tired too so just I'll throw out some suggestions for you to think about.
1) Simple group by and count for the first two columns. A corrolated subselect that counts the number of rows in the table where the memberid matches customerid.
2) Left outer join from your table to a derived table which is simply a select distinct of all the customerids in your table, joining the memberids to this distinct list of customerids. Group by and count(*) for your first two columns, count(TheDistinctCustomerIDs) for your third column.