MySQL query to find complete intersections

Question

Imagine we have two tables:

clientOffices that have a list of offices that each client has.
clusters are groups of cities

clientOffices
-----------------------------
clientId    |   office
-----------------------------
1               London
1               Manchester
1               Edinburgh
1               Bonn
2               London
2               Frankfurt
2               Bonn
3               Manchester
3               Frankfurt
-----------------------------       



clusters
-----------------------------
clusterName |   city
-----------------------------
X               London      
X               Manchester
Y               Manchester
Y               Frankfurt
Y               Bonn
Z               London
Z               Bonn
-----------------------------

Now we want a query that tells us this: For each client, which what clusters are they FULLY present in? i.e. they have an office in EVERY city in the cluster?

Desired result:
-------------------------------------------
clientOffices.clientId  |   clusters.clusterName
-------------------------------------------
1                               X
1                               Z
2                               Z
3                               Null
--------------------------------------------

I'm a MySQL Noob - tried different types of joins but couldn't get this result. It'd be great if you could help.

fiddle: https://www.db-fiddle.com/f/2GTBVXm9StNHcyuSqSZikW/0

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Oct 04 '20 at 06:31
@O.Man, The 'office' column is connected to the 'city' column. Does that answer your question? — Saurabh, Oct 04 '20 at 06:35
@Saurabh When you want to make a join between 2 tables you need an attribute of connection. Show us data model. — O.Man, Oct 04 '20 at 06:37
Please: Put what is needed to ask in your post, not just at a link. — philipxy, Oct 05 '20 at 01:56
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, **chop code to the 1st expression not giving what you expect & say what you expect & why.** — philipxy, Oct 05 '20 at 02:08
@O.Man "When you want to make a join between 2 tables you need an attribute of connection." Not so. Eg cross join. Eg inner/outer join is on a condition--any condition. Constraints including PKs & FKs need not hold, be known or be declared to query. Table (base & query result) meanings (aka row membership conditions aka predicates) are necessary & sufficient to query. So I'd agree we need to know the meanings of the input/base & output tables. So: When giving a business relation(ship)/association or table, say what a row in it states about the business situation in terms of its column values. — philipxy, Oct 05 '20 at 02:13

score 1 · Accepted Answer · answered Oct 04 '20 at 07:10

This will show all the client names that have complete cluster

SELECT DISTINCT a.clientName, a.clusterName
FROM
(
    SELECT clientOffices.clientName, clusters.clusterName, COUNT(*) AS counts
    FROM clientOffices
    LEFT JOIN clusters ON clusters.city = clientOffices.office
    GROUP BY clientOffices.clientName, clusters.clusterName
) AS a
JOIN
(
    SELECT clusterName, COUNT(*) AS counts
    FROM clusters
    GROUP BY clusterName
) AS b
ON a.clusterName = b.clusterName AND a.counts = b.counts

UNION

SELECT c.clientName, null AS clusterName FROM
(
  SELECT  a.clientName, COUNT(*) as invalid_office
FROM
(
    SELECT clientOffices.clientName, clusters.clusterName, COUNT(*) AS counts
    FROM clientOffices
    LEFT JOIN clusters ON clusters.city = clientOffices.office
    GROUP BY clientOffices.clientName, clusters.clusterName
) AS a
JOIN
(
    SELECT clusterName, COUNT(*) AS counts
    FROM clusters
    GROUP BY clusterName
) AS b
ON a.clusterName = b.clusterName AND a.counts != b.counts
  GROUP BY a.clientName
) AS c
JOIN
(
    SELECT clientName, COUNT(*) AS office_count
    FROM clientOffices
    GROUP BY clientName
) AS d
ON c.clientName = d.clientName AND c.invalid_office = d.office_count;

https://www.db-fiddle.com/f/6jKvKXPYvsLeXgm3Qv1nHu/10

WOW - I had no idea this would be so complex a query - this is working, thanks a lot! — Saurabh, Oct 04 '20 at 07:14
@Saurabh . . . Such a complex query is *not* necessary. I would advise you to look at the other answer. — Gordon Linoff, Oct 04 '20 at 13:23

score 1 · Answer 2 · answered Oct 04 '20 at 11:26

1

This reads like a relational division problem. I would write this as an aggregation query, with filtering in the having clause:

select co.clientid, cl.clusterName
from clientoffices co
inner join clusters cl on cl.city = co.office
group by co.clientid, cl.clusterName
having count(*) = (select count(*) from clusters cl1 where cl1.clusterName = cl.clusterName)

For your sample data this produces:

| clientid | clusterName |
| -------- | ----------- |
| 1        | X           |
| 1        | Z           |
| 2        | Z           |

answered Oct 04 '20 at 11:26

GMB

216,147
25
84
135

last row is missing where clientid is 3 and clusterName is null – aRvi Oct 04 '20 at 13:39
@aRvi: the desired results is not really consistent with the question itself already (all rows relate to client `1`, which is obviously not what OP meant). My answer is meatnt o adresses the question that was phrased as: *For each client, what clusters are they FULLY present in*. – GMB Oct 04 '20 at 13:43

MySQL query to find complete intersections

2 Answers2