n:m selection of all Entities in n which have more then one FK in relationship

I have to Tables A and B which have a relationship of n:m. So i have a table C to store the pairs of the foreigen Keys of A and B. I need a query which give me all Entries from A which have a Pair with 2 or more Attributes in B. Exampl开发者_如何学Ce. First is the ID second stand for all attributes the tables can have.

+-----------+
|     A     |
+-----------+
| 1 fields  |
| 2 fields  |
| ...       |
| n fields  |
+-----------+

+-----------+
|     B     |
+-----------+
| 1 fields  |
| 2 fields  |
| ...       |
| n fields  |
+-----------+

+-----------+
|     c     |
| FKA | FKB |
+-----------+
| 1      2  | <-
| 2      2  | <-
| 2      3  |
| 1      4  | <-
| 2      4  | <-
| 3      2  |
| 6      4  |
+-----------+

How can i select all Datasets in A which have a relationship to the dataset 2 AND 4 in Table B?

---

Basically, your asking to get all ID's from the A table which have two or more entries in table C, so you can use:

SELECT COUNT(1) AS cnt, FKA 
FROM c 
GROUP BY FKA 
    HAVING cnt >= 2

I'm not sure I understand correctly, but I think you also want a list of all the values of FKB that are in table C for each FKA, for which you can use:

SELECT COUNT(1) AS cnt, FKA, GROUP_CONCAT(FKB SEPERATOR ",")
FROM c 
GROUP BY FKA 
    HAVING cnt >= 2

I haven't tested the queries, so maybe there are syntax errors, but the idea should be ok.

EDIT; answer to real question (I hope) Suppose you have list of 4 ID's of table B (ie. 1, 4, 67 and 133) and you want to know which ID's of table A link to all of those; you could use:

SELECT COUNT(1) AS cnt, FKA 
FROM c 
WHERE (FKB = 1 OR FKB = 4 OR FKB = 67 OR FKB = 133)
GROUP BY FKA 
    HAVING cnt = 4

This only works if the combination of FKA and FKB is unique in table c (which would be a good practice anyway.)

---

This is where a join helps:

SELECT *
FROM A RIGHT OUTER JOIN B ON A.ID = B.ID

This is the basic SQL you would need, and conversely you could do

SELECT *
FROM B LEFT OUTER JOIN A ON A.ID = B.ID