mardi 21 juin 2016

How do I query for records that all have the same relationships (MS Access)?

I'm trying to query for records that share common relationships. In order to avoid gratuitous context, here is a hypothetical example from my favorite old-school Nintendo game:

Consider a table of boxers:

tableBoxers
ID  | boxerName
----------------
1   | Little Mac
2   | King Hippo
3   | Von Kaiser
4   | Don Flamenco
5   | Bald Bull

Now I have a relationship table that links them together

boxingMatches
boxerID1   |  boxerID2
-----------------------
1          |  3
2          |  5
2          |  4
5          |  1
4          |  1

Since I don't want to discriminate between ID1 and ID2, I create a query that UNIONs them together:

SELECT firstID AS boxerID1, secondID AS boxerID2 FROM
(
  SELECT boxerID1 AS firstID, boxerID2 AS boxerID FROM boxingMatches
  UNION ALL
  SELECT boxerID2 AS firstID, boxerID1 AS secondID FROM boxingMatches
) ORDER  BY firstID, secondID

I get:

queryBoxingMatches
boxerID1   |  boxerID2
-----------------------
1          |  3
1          |  4
1          |  5
2          |  4
2          |  5
3          |  1
4          |  1
4          |  2
5          |  1
5          |  2

Now I have VBA script where a user can select the boxers he's interested in. Let's say he selects Little Mac (1) and King Hippo (2). This gets appended into a temporary table:

summaryRequest
boxerID
--------
1
2

Using table [summaryRequest] and [queryBoxingMatches], how do I find out whom Little Mac (1) and King Hippo (2) have similarly fought against? The result should be Bald Bull (5) and Don Flamenco (4).

Bear in mind that [summaryRequest] could have 0 or more records. I have considered an INTERSECT, but I'm not sure that's the right function for this. I've tried using COUNT numerous ways, but it gives undesired data when there are multiple relationships (e.g. if Little Mac fought Bald Bull twice and King Hippo only fought him once).

I can't help but feel like the answer is plain and simple and I'm just overthinking it. Any help is appreciated. Thanks.

Aucun commentaire:

Enregistrer un commentaire