mysql - select from table depending on entries in other table

Question

This is the setup:

Table A has a connection to table B

there are multiple entries (0 to n) in table B that can have a matching record in table A

How do I form a query that gives me a record from table A only if a certain amount of matching records exist in table B?

Example:

Table A has clothing in it Table B has attributes for clothing

table B has a foreign key to table A so it would 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 want only the clothes which have attribute 'attributeA' AND 'attributeB' AND 'attributeC'

This is not a problem if I do a OR-query but I can't 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 evaluate to true... how do I do that?

Answer 1

You could do it with 3 inner joins... i.e. give me table A rows that have the attributes I want

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

May be this one.. have to try it

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 3

Another way to do this is as follows, which requires zero joins, but a nested sub-query... have tries this on a dummy database, and it seems to do the job. Hope it helps.

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
            )

Answer 4

I would use GROUP_CONCAT to fetch a single row of all attributes for a given piece of clothing:

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

Giving something like:

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

From this resultset, you could select those ids for which the concatenated attribute matched the set of attributes you were looking for.

select id from MyConcatenatedResults where Attributes = 'A,B,C'