Mysql – Select from table depending on entries in other table


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?


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 =
WHERE attribute='A' AND attribute='B' AND attribute='C'

This condition will never evaluate to true. How do I do that?

Best Solution

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

INNER JOIN tableB BA ON = BA.fid_clothing AND BA.Attribute='A'
INNER JOIN tableB BB ON = BB.fid_clothing AND BB.Attribute='B'
INNER JOIN tableB BC ON = BC.fid_clothing AND BC.Attribute='C'
Related Question