Selection from the table depending on the entries in another table

Question

This is the setting:

Table A has a connection to table B. Table B has several entries (from 0 to n) that may have a corresponding entry in table A.

How to generate a query that gives me an entry from table A only if a certain number of matching records exists in table B?

Example:

Table A has clothes. Table B has attributes for clothing.

Table B has a foreign key for table A, so it will look something like this:

id  fid_clothing1  attributeA
id  fid_clothing1  attributeB
id  fid_clothing1  attributeC
id  fid_clothing2  attributeA
id  fid_clothing2  attributeB

Now I only want clothes that have the attributeAAND attributeBAND attribute attributeC. This is not a problem if I make an OR request, but I cannot just do something like:

SELECT * from tableA
LEFT JOIN tableB on tableB.fid_cloting = tableA.id
WHERE attribute='A' AND attribute='B' AND attribute='C'

This condition will never be true. How to do it?

Answer 1

3 ... , . , .

SELECT A.id FROM tableA A
INNER JOIN tableB BA ON A.id = BA.fid_clothing AND BA.Attribute='A'
INNER JOIN tableB BB ON A.id = BB.fid_clothing AND BB.Attribute='B'
INNER JOIN tableB BC ON A.id = BC.fid_clothing AND BC.Attribute='C'
GROUP BY A.id

Answer 2

GROUP_CONCAT, :

SELECT id,GROUP_CONCAT(attribute order by attribute) as Attributes 
FROM tableB 
GROUP BY id;

- :

| id | Attributes |
|  1 | A,B,C      |

, , .

id MyConcatenatedResults, Attributes = 'A, B, C'

Answer 3

, ... .

SELECT * FROM TABLEA a, TABLE b 
WHERE a.id = b.clothing_id 
AND a.id in (SELECT clothing_id from TABLEB where attribute = 'A' AND clothing_id = a.id
UNION select clothing_id from TABLEB where attribute = 'B' AND clothing_id = a.id
UNION select clothing_id from TABLEB where attribute = 'C' AND clothing_id = a.id)

Answer 4

, , ... , , . , .

SELECT * 
FROM tableA A 
WHERE id IN ( 
              SELECT clothing_id
              FROM tableB B
              WHERE
                attribute =  "A"
                OR attribute = "B"
                OR attribute = "C"
              GROUP BY clothing_id
              HAVING count (*) = 3
            )